Using PLINQ for multiple ID lookups

Question

I am using entity framework to connect to a SQL server database.

Lets say I have a list of SQL table primary keys to lookup.

List<string> idList

I could return the list of all the matching rows using

var results = (from id in idList
              from row in db.database
              where row.Id == id
              select row).ToList();

In order to make things more interesting, I am considering using this.

var results = (from id in idList
              from row in db.database.AsParallel()
              where row.Id == id
              select row).ToList();

Am I correct to say that this will cause each ID lookup to be queried in parallel? Or is there a better way to speed up multiple ID lookups? Please enlighten me.

Answer 1

Assuming you're in the usual situation, that is, idList is relatively short and db.database is relatively long, you don't want to parallelize your query. What you want to do is to execute the query on the database. To do that, you could change your query to:

from row in db.Rows
where idList.Contains(row.Id)
select row

which generates efficient SQL like:

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Translation] AS [Translation]
    FROM [dbo].[Rows] AS [Extent1]
    WHERE [Extent1].[Id] IN (N'42', N'13')

instead of:

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Translation] AS [Translation]
    FROM [dbo].[Rows] AS [Extent1]

which retrieves the whole table from the database and then performs filtering on it (in parallel or not).