C# csharp « DucDigital

11Dec/090

Query a set of IDs from SQL using LINQ to SQL

Just not so long ago, I have a hard time while doing this in LINQ to SQL since i have my datacontext running on LINQ to SQL. I was force to do a N+1 querry, which is extremely expensive on both CPU and Time since i put my SQL Server else where that not in local and each query cost me 200ms in time.

My N+1 query was look like this:

SELECT * FROM Image WHERE IMG_ID = 1
SELECT * FROM Image WHERE IMG_ID = 3
SELECT * FROM Image WHERE IMG_ID = 7
SELECT * FROM Image WHERE IMG_ID = 9

however, later i found out a way to solve this by using "Contains" from IList to query "WHERE IN(x,x,x)".

Here is one function in my Repository:

public List<Image> Images(IList<int> idList)
{
     return (from d in _db.Images
               .Where<Image>(d => idList.Contains<int>(d.IMG_ID))
               select d).ToList<Image>();
}

or better:

1	return _db.Images.Where<Image>(x => idList.Contains<int>(x.IMG_ID)).ToList<Image>();

these will output:

SELECT * FROM Image WHERE IMG_ID IN(1,3,7,9)

Tagged as: C#, C# csharp, linq, linq to sql, mssql, query, sql, where No Comments

Licenses Status

VBB 4 Available: 0
VBB 4 Sold: 3
VBB 4 Suite Available: 0
VBB 4 Suite Sold: 9
VBB 3 Available: 0
VBB 3 Sold: 6
VbSeo Available: 0
VBSeo Sold: 4
IPB Available: 5
IPB Sold: 4

Spam cleared

9,368 spam comments blocked by
Akismet

DucDigital for ( $girl = 1; $girl < $required; $girl++ ) { echo “I love DucDigital”; }

Query a set of IDs from SQL using LINQ to SQL

Licenses Status

Recent Posts

Recent Comments

Categories

Tags

Blogroll

Spam cleared

Who's Online