There is an entity Projects. Each element of this entity is of type Project.

From the neighboring database I create a List<Project> via a DataReader (since there is no Entity Framework on that base) and I want to make a comparison with my entity.

I need to get items that are in my essence, but not in the neighboring database.

I tried to do this thing:

 var delProjects = db.Projects.Except(listProjects).ToList();

But the error takes off:

An unhandled exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll

Additional information: Unable to create a value of type 'WCFConsoleServer.Models.Project'. Only primitive types or enumeration types are supported in this context.

Actually the question is how to avoid this error?

I think that the problem should be solved if the whole entity is loaded into memory in the List and only then do the subtraction of the set. But isn’t the performance going to be strong?

You can obviously shoot all this in the foreach and compare, but it seems to me too slowly.

Are there any other solutions?

upd

Such a construction produces a similar error.

 db.Projects.Where(z=>!listProjects.All(y=>y.id!=x.id)).ToList();

Although it should be interpreted, in my opinion, as

 foreach foreach if
  • "You can obviously shoot all this in the foreach and compare, but it seems to me too slowly." - Do you think that Except somehow works differently? Implement to start IEqualityComparer . In fact, you have already been answered - Dmitry
  • It seemed to me that in this case the List is sent to the SQL-server as a table variable and subtraction is performed. - iluxa1810
  • try to look in the debugger that goes to the server, you'll be incredibly surprised - Dmitry
  • Entity is of course convenient, but the mechanism of forming and processing requests makes this tool only a guide for studying Linq. - Dmitry
  • And unless that I wrote in UPD should not work without implementations of interfaces? - iluxa1810

2 answers 2

Except - finds the difference of the sets represented by two sequences, using the comparator for equality checks to compare the values ​​by default. If you want to compare two sequences of objects of a particular user-defined data type, you need to implement the IEqualityComparer interface universal in the auxiliary class. The following code example shows how to implement this interface in a user-defined data type and provide the GetHashCode and Equals methods. MSDN

  • Updated the question. In this approach, I compare the primitive types, and get a similar error - iluxa1810
  • Try this var exceptProject = from dbproject in db.Projects from projectlist in listProjects where dbproject.id! = Projectlist.id select projectlist; - Evgeny Kidyaev

The problem is that you create the entities from a third-party source and try to pass them to the request. EF does not know how. EF can only transfer primitive types or sequences of primitive types to a query.

You need to first collect a list of their keys into a separate variable:

 var listProjectIds = listProjects.Select(y => y.id); db.Projects.Where(x => !listProjectIds.Contains(x.id)).ToList(); // вот так работает

Option two is not to transfer the list of excluded keys to the server in the request, but to pull everything out and filter it on the client:

 db.Projects.AsEnumerable().Where(x => !listProjects.Any(y => y.id == x.id)).ToList(); // и вот так тоже работает