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

huangapple go评论57阅读模式
英文:

How to Order Conditionally in Linq EF Core?

问题

如何有条件地编写 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

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

  1. IQueryable 在调用枚举运算符之前不执行查询。
  2. 如果使用了 Select,则会忽略 Include()
  3. 如果通过 Select 生成自定义实体,则不需要使用 AsNoTracking()
  4. 无需检查空导航属性,EF Core 会处理这个问题。
  5. 最好返回 IQueryable,然后可以应用 CountAsyncToArrayAsyncToListAsync 等操作。
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.

  1. IQueryable do not execute query until you call enumeration operator
  2. Include() is ignored if you have Select
  3. AsNoTracking() is not needed if you produce custom entity via Select
  4. You do not have to check for null navigation properties, EF Core will handle this.
  5. 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)));

huangapple
  • 本文由 发表于 2023年6月19日 20:08:21
  • 转载请务必保留本文链接:https://go.coder-hub.com/76506479.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定