Linq Query Where Contains

Question

I'm attempting to make a linq where contains query quicker. The data set contains 256,999 clients. The Ids is just a simple list of GUID'S and this would could only contain 3 records.

The below query can take up to a min to return the 3 records. This is because the logic will go through the 256,999 record to see if any of the 256,999 records are within the List of 3 records.

returnItems = context.ExecuteQuery<DataClass.SelectClientsGridView>(sql).Where(x => ids.Contains(x.ClientId)).ToList();

I would like to and get the query to check if the three records are within the pot of 256,999. So in a way this should be much quicker.

I don't want to do a loop as the 3 records could be far more (thousands). The more loops the more hits to the db.

I don't want to grap all the db records (256,999) and then do the query as it would take nearly the same amount of time.

If I grap just the Ids for all the 256,999 from the DB it would take a second. This is where the Ids come from. (A filtered, small and simple list)

Any Ideas?

Thanks

If your are using raw sql you could do it in sql itself .`select Id from TableA where Id in( 11,12,13)` Proper index in your db will improve the performance — Eldho, May 06 '16 at 12:03
The type returned from the the Execute query is a data class, which I have created to match the SQL SELECT. Like you would have for a complex linq — Chris Cooper, May 06 '16 at 13:05
You could try `IndexOf()>=0`, just in case its [better support](http://stackoverflow.com/a/24821781/256431) helps here too. (Of course, it shouldn't :-| ) — Mark Hurd, May 24 '16 at 10:56

score 0 · Answer 1 · answered May 07 '16 at 13:36

You've said "I don't want to grab all the db records (256,999) and then do the query as it would take nearly the same amount of time," but also "If I grab just the Ids for all the 256,999 from the DB it would take a second." So does this really take "just as long"?

returnItems = context.ExecuteQuery<DataClass.SelectClientsGridView>(sql).Select(x => x.ClientId).ToList().Where(x => ids.Contains(x)).ToList();

Unfortunately, even if this is fast, it's not an answer, as you'll still need effectively the original query to actually extract the full records for the Ids matched :-(

So, adding an index is likely your best option.

score 0 · Answer 2 · answered May 09 '16 at 08:11

The reason the Id query is quicker is due to one field being returned and its only a single table query. The main query contains sub queries (below). So I get the Ids from a quick and easy query, then use the Ids to get the more details information.

SELECT  Clients.Id as ClientId, Clients.ClientRef as ClientRef, Clients.Title + ' ' + Clients.Forename + ' ' + Clients.Surname as FullName, 
            [Address1] ,[Address2],[Address3],[Town],[County],[Postcode], 
            Clients.Consent AS Consent,
            CONVERT(nvarchar(10), Clients.Dob, 103) as FormatedDOB, 
            CASE WHEN Clients.IsMale = 1 THEN 'Male' WHEN Clients.IsMale = 0 THEN 'Female' END As Gender, 
            Convert(nvarchar(10), Max(Assessments.TestDate),103) as LastVisit, ";
            CASE WHEN Max(Convert(integer,Assessments.Submitted)) = 1 Then 'true' ELSE 'false' END AS Submitted, 
            CASE WHEN Max(Convert(integer,Assessments.GPSubmit)) = 1 Then 'true' ELSE 'false' END AS GPSubmit, 
            CASE WHEN Max(Convert(integer,Assessments.QualForPay)) = 1 Then 'true' ELSE 'false' END AS QualForPay, 
            Clients.UserIds AS LinkedUsers
              FROM Clients 
            Left JOIN Assessments ON Clients.Id = Assessments.ClientId
            Left JOIN Layouts ON Layouts.Id = Assessments.LayoutId
            GROUP BY Clients.Id, Clients.ClientRef, Clients.Title, Clients.Forename, Clients.Surname, [Address1] ,[Address2],[Address3],[Town],[County],[Postcode],Clients.Consent, Clients.Dob, Clients.IsMale,Clients.UserIds";//,Layouts.LayoutName, Layouts.SubmissionProcess
            ORDER BY ClientRef

I was hoping there was an easier way to do the Contain element. As the pool of Ids would be smaller than the main pool.

A way I've speeded it up for now is. I've done a Stinrg.Join to the list of Ids and added them as a WHERE within the main SQL. This has reduced the time down to a seconds or so now.

Linq Query Where Contains

2 Answers2