英文:
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<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);
}
答案1
得分: 1
不需要在这里使用编译查询,一切都可以简化。
IQueryable
在调用枚举运算符之前不执行查询。- 如果使用了
Select
,则会忽略Include()
。 - 如果通过
Select
生成自定义实体,则不需要使用AsNoTracking()
。 - 无需检查空导航属性,EF Core 会处理这个问题。
- 最好返回
IQueryable
,然后可以应用CountAsync
、ToArrayAsync
、ToListAsync
等操作。
public IQueryable<Dictionary> GetDictionaryData(int page, float limit, string? sort, string? name)
{
var query = _dbContext.DictionaryMasterUpdateInfo
.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.DataSetName ?? "",
DestinationDataSetName = x.DictionaryMaster.DestinationDataSetMaster.DataSetName ?? "",
});
switch (sort)
{
case "dictionary_name" : query = query.OrderBy(x => x.DictionaryName); break;
case "destination_dataset_name" : query = query.OrderBy(x => x.DestinationDataSetName); break;
default : query = query.OrderBy(x => x.DictionaryName)
.ThenByDescending(dm => 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 operatorInclude()
is ignored if you haveSelect
AsNoTracking()
is not needed if you produce custom entity viaSelect
- You do not have to check for null navigation properties, EF Core will handle this.
- Better return
IQueryable
, then you can applyCountAsync
,ToArrayAsync
,ToListAsync
ect.
public IQueryable<Dictionary> GetDictionaryData(int page, float limit, string? sort, string? name)
{
var query = _dbContext.DictionaryMasterUpdateInfo
.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.DataSetName ?? "",
DestinationDataSetName = x.DictionaryMaster.DestinationDataSetMaster.DataSetName ?? "",
});
switch (sort)
{
case "dictionary_name" : query = query.OrderBy(x => x.DictionaryName); break;
case "destination_dataset_name" : query = query.OrderBy(x => x.DestinationDataSetName); break;
default : query = query.OrderBy(x => x.DictionaryName)
.ThenByDescending(dm => 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)
=>_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)));
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论