如何将外部列表集成到原始SQL查询中作为LINQ原始SQL查询中的表联接
技术问答
189 人阅读
|
0 人回复
|
2023-09-12
|
|
我在我的项目中有以下查询，每页运行10-20次。我试过用linq到sql，linq查询实体运行，但比它们快得多。8 G4 `1 Q7 m# i# G' d
问题是，如果我能用的话join外部列表语句（sContentIds）如果传输到查询中，会比使用更好SQL
! T" q; K# j" e3 @IN句子让查询更快吗？如果是这样，我怎么能做到这一点？sContentIds.Count在大多数情况下，它可能会在1到40之间发生变化。
; R+ U0 S+ n+ f7 xList cContents = unitOfWork.ExecuteQuery(@"SELECT c.ContentId,c.ContentPageId,c.CreatedById,p.PCA,p.PCC,p.PCD,c.AlbumId,a.AlbumTypeId FROM Contents c INNER JOIN Privatizations p ON c.ContentId = p.ContentId LEFT JOIN Albums a ON c.AlbumId = a.AlbumId WHERE c.ContentId IN (" string.Join(",",sContentIds) ")").ToList();我们正在研究ASP.NET MVC4框架，数据库交互采用工作单元模式。通常我已经建立了以下查询，但它比原始的要好sql查询慢5倍。! G& o2 O6 G4 d# I+ V
var cContents = unitOfWork.ContentRepository .GetFiltered(x => contentIds.Contains(x.ContentId)).Select(x => new filterContentsPCDTO() ContentId = x.ContentId, ContentPageId = x.ContentPageId, CreatedById = x.CreatedById, PCA = x.Privatization.PCA, PCC = x.Privatization.PCC, PCD = x.Privatization.PCD, PrivatizationModifiedById = x.Privatization.ModifiedById, AlbumId = x.AlbumId, albumTypeId = x.AlbumId == null ? -1 : x.Album.AlbumTypeId .ToList();GetFiltered方法的实现9 r: ^3 S* M( L h$ L9 ]% J" r
public IEnumerable GetFiltered( Expression> filter = null, Func[I],IOrderedQueryable> orderBy = null, string includeProperties = "") IQueryable query = _dbSet; if (filter != null) query = query.Where(filter); foreach (var includeProperty in includeProperties.Split (new char[' },StringSplitOptions.RemoveEmptyEntries)) query = query.Include(includeProperty); if (orderBy != null) return orderBy(query); else return query; . }) g% w+ w7 H7 h/ g9 a
解决方案: |
|
|
|
|
|
|
|
|
|
