CODEWITHSUNDEEP
c#linqlambda
SuryaKavitha
SuryaKavitha

Reputation: 523

Not able to compare two Lists using LINQ

I need to Compare two List types based on its column values Id and Name. List1 has names and ids and List2 also contains Id ,Name and other columns. Now I want to check a condition that one input Name receive in a method. That Name is not exist in List2 but It may exist in List1.The condition should validate that List1 Id does not exist in List2 Id and the external Name might exist in List1. I have used below query for achieve this. 

var IsNameExists= db.List1.Where(a => List2.Any(b=>b.Id!=a.Id) && a.Name== InputName).ToList();

When I am using this query, I am getting exception like

Unable to create a constant value of type 'ProjectName.DBEntities.List2'. Only primitive types or enumeration types are supported in this context.

I am not sure what i have done wrong on my query. Please guide me how can I achieve this to check the Name existence on List1.

Corrected query and Answer is:

var IsNameExists= db.List1.ToList().Where(a => !(List2.Select(x => x.Id).Contains(a.Id) && a.Name== InputName).ToList();

Upvotes: 0

Views: 223

Answers (1)

TheGeneral
TheGeneral

Reputation: 81503

You could do something like this i guess, basically its just selecting out the ids and sending them to SQL as primitives to make a nice simple sql query

The fundamental problem here is trying to mix whats in memory with LinqToSql which in essence tries to convert you statement to a query and runs it on the database. The database wont know about your List that you have memory, so the solve is either give it something it understands or do it in memory. The following example extracts the ids out, and gives the data base something it does know, a list of int 

var ids = List2.Select(x => x.Id).ToList();

var IsNameExists= db.List1.Where(a => !ids.Contains(a.Id) && a.Name== InputName)
                         .ToList();

Upvotes: 1

Related Questions