如何将外部列表集成到原始SQL查询中作为LINQ原始SQL查询中的表联接 [英] How to integrate external list to raw sql query join as table in a linq raw sql query
问题描述
我在我的项目中有以下查询,每页运行10-20次.我试图使用linq到sql,linq到实体运行此查询,但这比它们快得多.
i have the following query running 10-20 times / page in my project. i have tried to run this query with linq to sql, linq to entities but this is far more faster then them.
问题是,如果我可以使用join语句将外部列表(sContentIds)传递到查询中,那么它会比使用SQL IN语句使查询更快吗?如果是这样,我怎么能做到这一点. sContentIds.Count多数情况下可能在1到40之间变化.
The question is if i could pass in the external list(sContentIds) into query with a join statement, would it make the query faster then using SQL IN statement? If so how can i achieve this. sContentIds.Count may vary from 1-40 most of the times.
List<filterContentsPCDTO> cContents = unitOfWork.ExecuteQuery<filterContentsPCDTO>(@"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倍.
We are working on ASP.NET MVC4 framework and using unit of work pattern for database interactions. Normally i had built this query like follows but it was 5 times slower then raw sql query.
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方法的实现
Implementation of GetFiltered Method
public IEnumerable<T> GetFiltered(
Expression<Func<T, bool>> filter = null,
Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null,
string includeProperties = "")
{
IQueryable<T> 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;
}
}
推荐答案
如果您使用的是SQL Server 2008(或更高版本),并且提高性能是此处的主要目标(并且您可能愿意放弃LINQ to SQL,在这种情况下),我建议将此查询编写为采用用户定义的表类型作为参数.这将使您可以将整个sContentIds
集合传递到数据库,并且仍然可以受益于存储过程相对于即席查询的优势.
If you're using SQL Server 2008 (or newer) and increasing performance is the main objective here (and you're maybe willing to abandon LINQ to SQL for this scenario), I would recommend writing this query as a stored procedure that takes a user-defined table type as a parameter. This will allow you to pass your entire sContentIds
collection to the database and still benefit from advantages of a stored procedures over an ad-hoc query.
首先,将表类型定义为:
First, define the table type as something like:
CREATE TYPE [dbo].[ContentList] AS TABLE(
[ContentId] [int]
)
然后按照以下方式创建过程:
Then create the procedure as something like:
CREATE PROCEDURE [dbo].[usp_GetContents]
@contentIds ContentList READONLY
AS
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 (SELECT ContentId FROM @contentIds)
然后,您应该能够使用在此答案中描述的技术从C#调用它(基本上,创建一个DataTable
,然后将其添加为常规参数).不幸的是,它看起来这很难用LINQ to SQL来实现,但是,正如我所说,提高性能是主要的目标,这可能是一个选择.
Then you should be able to call it from C# using the technique described in this answer (basically, create a DataTable
from your list then add it like a regular parameter). Unfortunately it looks like this is tough to do with LINQ to SQL, but, as I said, if increasing performance is the main goal, this could be an option.
这篇关于如何将外部列表集成到原始SQL查询中作为LINQ原始SQL查询中的表联接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!