How to integrate external list to raw sql query join as table in a linq raw sql query

Question

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.

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();

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();

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;
            }
        }

Answer 1

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)

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.