问题

如何有条件地编写 OrderBy？
正如您在 if 和 else 条件中都可以看到，linq 的整体结构是相同的，只有 orderBy 不同。
在第一种情况下，我按升序排序，在第二种情况下，我按降序排序。
如何结合这两个条件，以避免代码的冗余？

另外，我想从数据库中获取经过筛选的数据，因此不想将查询存储在变量中，然后后续应用 orderBy。

public IAsyncEnumerable<Dictionary> GetDictionaryData(int page, float limit, string? sort, string? name)
{
    Func<EDMDbContextSQLServer, IAsyncEnumerable<Dictionary>>? query;
    if (sort == "dictionary_name" || sort == "destination_dataset_name")
    {
        query = EF.CompileAsyncQuery((EDMDbContextSQLServer _dbContext)
            => _dbContext.DictionaryMasterUpdateInfo
                .Include(dm => dm.DictionaryMaster)
                    .ThenInclude(dm => dm == null ? null : dm.SourceDataSetMaster)
                .Include(dm => dm.DictionaryMaster)
                    .ThenInclude(dm => dm == null ? null : dm.DestinationDataSetMaster)
                .AsNoTracking()
                .Select(x => new Dictionary
                {
                    DictionaryId = x.DictionaryId,
                    DictionaryName = x.DictionaryMaster!.DictionaryName,
                    DestinationDatasetId = x.DictionaryMaster.DestinationDatasetId,
                    SourceDatasetId = x.DictionaryMaster.SourceDatasetId,
                    IsStandard = x.DictionaryMaster.IsStandard,
                    SourceDataSetName = x.DictionaryMaster.SourceDataSetMaster != null ? x.DictionaryMaster.SourceDataSetMaster.DataSetName : "",
                    DestinationDataSetName = x.DictionaryMaster.DestinationDataSetMaster == null ? "" : x.DictionaryMaster.DestinationDataSetMaster.DataSetName,
                })
                .OrderBy(x => sort == "dictionary_name" ? x.DictionaryName : x.DestinationDataSetName)
                .Skip((page - 1) * (int)limit)
                .Take((int)limit));
    }
    else
    {
        query = EF.CompileAsyncQuery((EDMDbContextSQLServer _dbContext)
            => _dbContext.DictionaryMasterUpdateInfo
                .Include(dm => dm.DictionaryMaster)
                    .ThenInclude(dm => dm == null ? null : dm.SourceDataSetMaster)
                .Include(dm => dm.DictionaryMaster)
                    .ThenInclude(dm => dm == null ? null : dm.DestinationDataSetMaster)
                .AsNoTracking()
                .Select(x => new Dictionary
                {
                    DictionaryId = x.DictionaryId,
                    DictionaryName = x.DictionaryMaster!.DictionaryName,
                    DestinationDatasetId = x.DictionaryMaster.DestinationDatasetId,
                    SourceDatasetId = x.DictionaryMaster.SourceDatasetId,
                    IsStandard = x.DictionaryMaster.IsStandard,
                    SourceDataSetName = x.DictionaryMaster.SourceDataSetMaster != null ? x.DictionaryMaster.SourceDataSetMaster.DataSetName : "",
                    DestinationDataSetName = x.DictionaryMaster.DestinationDataSetMaster == null ? "" : x.DictionaryMaster.DestinationDataSetMaster.DataSetName,
                })
                .OrderByDescending(dm => dm.ModifiedOn!.Value.Date)
                .ThenByDescending(dm => dm.ModifiedOn!.Value.TimeOfDay)
                .ThenBy(x => x.DictionaryName)
                .Skip((page - 1) * (int)limit)
                .Take((int)limit));
    }
    return query(_dbContext);
}

英文:

How can I write OrderBy conditionally?
As you can see in both if and else condition entire linq is same only orderBy is different.
In first case I am ordering in ascending and in second case I am ordering in descending
How to combine both of these condition so that I can avoid redundancy of code?

Also I want to fetch filtered data from database so I don't want to store query in variable and apply orderBy later.

public IAsyncEnumerable&lt;Dictionary&gt; GetDictionaryData(int page,float limit,string? sort,string? name)
{  
    Func&lt;EDMDbContextSQLServer, IAsyncEnumerable&lt;Dictionary&gt;&gt;? query;      
    if(sort==&quot;dictionary_name&quot; || sort==&quot;destination_dataset_name&quot;)
    {
        query = EF.CompileAsyncQuery((EDMDbContextSQLServer _dbContext)
                =&gt; _dbContext.DictionaryMasterUpdateInfo
                    .Include(dm =&gt; dm.DictionaryMaster)
                        .ThenInclude(dm =&gt; dm==null ? null : dm.SourceDataSetMaster)
                    .Include(dm =&gt; dm.DictionaryMaster)
                        .ThenInclude(dm =&gt; dm==null ? null : dm.DestinationDataSetMaster)
                    .AsNoTracking()
                    .Select(x =&gt; new Dictionary
                    {
                        DictionaryId = x.DictionaryId,
                        DictionaryName = x.DictionaryMaster!.DictionaryName,
                        DestinationDatasetId = x.DictionaryMaster.DestinationDatasetId,
                        SourceDatasetId = x.DictionaryMaster.SourceDatasetId,
                        IsStandard = x.DictionaryMaster.IsStandard,
                        SourceDataSetName = x.DictionaryMaster.SourceDataSetMaster != null ? x.DictionaryMaster.SourceDataSetMaster.DataSetName : &quot;&quot;,
                        DestinationDataSetName = x.DictionaryMaster.DestinationDataSetMaster == null ? &quot;&quot; : x.DictionaryMaster.DestinationDataSetMaster.DataSetName,
                    })
                    .OrderBy(x=&gt;sort==&quot;dictionary_name&quot; ? x.DictionaryName : x.DestinationDataSetName)
                    .Skip((page - 1) * (int)limit)
                    .Take((int)limit));
    }
    else
    {              
        query = EF.CompileAsyncQuery((EDMDbContextSQLServer _dbContext)
                =&gt;_dbContext.DictionaryMasterUpdateInfo
                    .Include(dm =&gt; dm.DictionaryMaster)
                        .ThenInclude(dm =&gt; dm==null ? null : dm.SourceDataSetMaster)
                    .Include(dm =&gt; dm.DictionaryMaster)
                        .ThenInclude(dm =&gt; dm==null ? null : dm.DestinationDataSetMaster)
                    .AsNoTracking()
                    .Select(x =&gt; new Dictionary
                    {
                        DictionaryId = x.DictionaryId,
                        DictionaryName = x.DictionaryMaster!.DictionaryName,
                        DestinationDatasetId = x.DictionaryMaster.DestinationDatasetId,
                        SourceDatasetId = x.DictionaryMaster.SourceDatasetId,
                        IsStandard = x.DictionaryMaster.IsStandard,
                        SourceDataSetName = x.DictionaryMaster.SourceDataSetMaster != null ? x.DictionaryMaster.SourceDataSetMaster.DataSetName : &quot;&quot;,
                        DestinationDataSetName = x.DictionaryMaster.DestinationDataSetMaster == null ? &quot;&quot; : x.DictionaryMaster.DestinationDataSetMaster.DataSetName,
                    })
                    .OrderByDescending(dm =&gt; dm.ModifiedOn!.Value.Date)
                    .ThenByDescending(dm =&gt; dm.ModifiedOn!.Value.TimeOfDay)
                    .ThenBy(x=&gt;x.DictionaryName)
                    .Skip((page - 1) * (int)limit)
                    .Take((int)limit));
        


    }
    return query(_dbContext);
}

答案1

得分: 1

不需要在这里使用编译查询，一切都可以简化。

IQueryable 在调用枚举运算符之前不执行查询。
如果使用了 Select，则会忽略 Include()。
如果通过 Select 生成自定义实体，则不需要使用 AsNoTracking()。
无需检查空导航属性，EF Core 会处理这个问题。
最好返回 IQueryable，然后可以应用 CountAsync、ToArrayAsync、ToListAsync 等操作。

public IQueryable&lt;Dictionary&gt; GetDictionaryData(int page, float limit, string? sort, string? name)
{  
    var query = _dbContext.DictionaryMasterUpdateInfo
        .Select(x =&gt; new Dictionary
        {
            DictionaryId = x.DictionaryId,
            DictionaryName = x.DictionaryMaster!.DictionaryName,
            DestinationDatasetId = x.DictionaryMaster.DestinationDatasetId,
            SourceDatasetId = x.DictionaryMaster.SourceDatasetId,
            IsStandard = x.DictionaryMaster.IsStandard,
            SourceDataSetName = x.DictionaryMaster.SourceDataSetMaster.DataSetName ?? &quot;&quot;,
            DestinationDataSetName = x.DictionaryMaster.DestinationDataSetMaster.DataSetName ?? &quot;&quot;,
        });

    switch (sort)
    {
        case &quot;dictionary_name&quot; : query = query.OrderBy(x =&gt; x.DictionaryName); break;
        case &quot;destination_dataset_name&quot; : query = query.OrderBy(x =&gt; x.DestinationDataSetName); break;
        default : query = query.OrderBy(x =&gt; x.DictionaryName)
                     .ThenByDescending(dm =&gt; dm.ModifiedOn!.Value.TimeOfDay); 
           break;
    }

    query = query
        .Skip((page - 1) * (int)limit)
        .Take((int)limit);

    return query;
}

英文:

You don't need compiled query here and everything can be simplified.

IQueryable do not execute query until you call enumeration operator
Include() is ignored if you have Select
AsNoTracking() is not needed if you produce custom entity via Select
You do not have to check for null navigation properties, EF Core will handle this.
Better return IQueryable, then you can apply CountAsync, ToArrayAsync, ToListAsync ect.

public IQueryable&lt;Dictionary&gt; GetDictionaryData(int page, float limit, string? sort, string? name)
{  
    var query = _dbContext.DictionaryMasterUpdateInfo
        .Select(x =&gt; new Dictionary
        {
            DictionaryId = x.DictionaryId,
            DictionaryName = x.DictionaryMaster!.DictionaryName,
            DestinationDatasetId = x.DictionaryMaster.DestinationDatasetId,
            SourceDatasetId = x.DictionaryMaster.SourceDatasetId,
            IsStandard = x.DictionaryMaster.IsStandard,
            SourceDataSetName = x.DictionaryMaster.SourceDataSetMaster.DataSetName ?? &quot;&quot;,
            DestinationDataSetName = x.DictionaryMaster.DestinationDataSetMaster.DataSetName ?? &quot;&quot;,
        });

    switch (sort)
    {
        case &quot;dictionary_name&quot; : query = query.OrderBy(x =&gt; x.DictionaryName); break;
        case &quot;destination_dataset_name&quot; : query = query.OrderBy(x =&gt; x.DestinationDataSetName); break;
        default : query = query.OrderBy(x =&gt; x.DictionaryName)
                     .ThenByDescending(dm =&gt; dm.ModifiedOn!.Value.TimeOfDay); 
           break;
    }

    query = query
        .Skip((page - 1) * (int)limit)
        .Take((int)limit);

    return query;
}

答案2

得分: 0

你可以这样做：

something = EF.CompileAsyncQuery((EDMDbContextSQLServer _dbContext)
                =>_dbContext.DictionaryMasterUpdateInfo
                    .Include(dm => dm.DictionaryMaster)
                        .ThenInclude(dm => dm==null ? null : dm.SourceDataSetMaster)
                    .Include(dm => dm.DictionaryMaster)
                        .ThenInclude(dm => dm==null ? null : dm.DestinationDataSetMaster)
                    .AsNoTracking()
                    .Select(x => new Dictionary
                    {
                        DictionaryId = x.DictionaryId,
                        DictionaryName = x.DictionaryMaster!.DictionaryName,
                        DestinationDatasetId = x.DictionaryMaster.DestinationDatasetId,
                        SourceDatasetId = x.DictionaryMaster.SourceDatasetId,
                        IsStandard = x.DictionaryMaster.IsStandard,
                        SourceDataSetName = x.DictionaryMaster.SourceDataSetMaster != null ? x.DictionaryMaster.SourceDataSetMaster.DataSetName : "",
                        DestinationDataSetName = x.DictionaryMaster.DestinationDataSetMaster == null ? "" : x.DictionaryMaster.DestinationDataSetMaster.DataSetName,
                    });
query = (sort=="dictionary_name" || sort=="destination_dataset_name") ?
(something.OrderBy(x=>sort=="dictionary_name" ? x.DictionaryName : x.DestinationDataSetName)
                    .Skip((page - 1) * (int)limit)
                    .Take((int)limit))) :
(something.OrderByDescending(dm => dm.ModifiedOn!.Value.Date)
                    .ThenByDescending(dm => dm.ModifiedOn!.Value.TimeOfDay)
                    .ThenBy(x=>x.DictionaryName)
                    .Skip((page - 1) * (int)limit)
                    .Take((int)limit)));

英文:

You can do it like this:

something = EF.CompileAsyncQuery((EDMDbContextSQLServer _dbContext)
                =&gt;_dbContext.DictionaryMasterUpdateInfo
                    .Include(dm =&gt; dm.DictionaryMaster)
                        .ThenInclude(dm =&gt; dm==null ? null : dm.SourceDataSetMaster)
                    .Include(dm =&gt; dm.DictionaryMaster)
                        .ThenInclude(dm =&gt; dm==null ? null : dm.DestinationDataSetMaster)
                    .AsNoTracking()
                    .Select(x =&gt; new Dictionary
                    {
                        DictionaryId = x.DictionaryId,
                        DictionaryName = x.DictionaryMaster!.DictionaryName,
                        DestinationDatasetId = x.DictionaryMaster.DestinationDatasetId,
                        SourceDatasetId = x.DictionaryMaster.SourceDatasetId,
                        IsStandard = x.DictionaryMaster.IsStandard,
                        SourceDataSetName = x.DictionaryMaster.SourceDataSetMaster != null ? x.DictionaryMaster.SourceDataSetMaster.DataSetName : &quot;&quot;,
                        DestinationDataSetName = x.DictionaryMaster.DestinationDataSetMaster == null ? &quot;&quot; : x.DictionaryMaster.DestinationDataSetMaster.DataSetName,
                    });
query = (sort==&quot;dictionary_name&quot; || sort==&quot;destination_dataset_name&quot;) ?
(something..OrderBy(x=&gt;sort==&quot;dictionary_name&quot; ? x.DictionaryName : x.DestinationDataSetName)
                    .Skip((page - 1) * (int)limit)
                    .Take((int)limit))) :
(something..OrderByDescending(dm =&gt; dm.ModifiedOn!.Value.Date)
                    .ThenByDescending(dm =&gt; dm.ModifiedOn!.Value.TimeOfDay)
                    .ThenBy(x=&gt;x.DictionaryName)
                    .Skip((page - 1) * (int)limit)
                    .Take((int)limit)));

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

如何在Linq EF Core中有条件地排序？

问题

答案1

答案2

.Net TimezoneInfo.ConvertTime将UTC日期转换为萨摩亚时返回了不正确的值。

无法访问已释放的上下文实例 – 在首次运行时

如何使用LINQ从嵌套的JSON数据中提取特定值？

“basic” Entity query结果非常慢，这样做有什么问题？

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

发表评论