Linq query with Contains only works with IQueryable in an external variable

Question

I am using the Entity Framework with Linq to Entities, trying to select some data from my database. When I create a Linq query that uses the IQueryable<int>.Contains , it can only filter data if I use an external variable! Let me give you an example.

This code block works fine :

 var volumes = (from v in work.VolumeAdditiveRepository.All where v.AdditivesID == AdditivesID select v.MetricID); var metrics = from m in work.MetricRepository.All where !volumes.Contains(m.ID) select m; 

If you look closely, you can see that I am using the volumes variable inside this fragment, in where . If I copy the contents of this variable and paste it inside the metrics variable, resulting in the code below, it raises an error: "Unable to create a constant value of type 'CalculadoraRFS.Models.Domain.VolumeAditivo'. Only primitive types ('such as Int32, String, and Guid') are supported in this context." .

 var metrics = from m in work.MetricRepository.All where !(from v in work.VolumeAdditiveRepository.All where v.AdditivesID == AdditivesID select v.MetricID).Contains(m.ID) select m; 

How can I replace a variable with such an error ?! Am I doing (of course) something wrong? Thanks!

---

UPDATE:

Actually, I found that the problem with the repository or DbContext template is a problem, as @jhamm noted. The snapshot below also does not work:

 var query = from m in work._context.Metric where !(from v in work._context.VolumeAdditive where v.AdditivesID == AdditivesID select v.MetricID).Contains(m.ID) select m; 

But the snapshot below works. I just took the context from the UnitOfWork class, although it is very simply defined there: public CalculadoraRFSContext _context = new CalculadoraRFSContext(); .

 var _context = new CalculadoraRFSContext(); var query = from m in _context.Metric where !(from v in _context.VolumeAdditive where v.AdditivesID == AdditivesID select v.MetricID).Contains(m.ID) select m; 

Now I'm really confused about this! Shouldn't this work as expected ?!

Answer 1

I used LINQPad to use my first EF Database model for a similar type of query. Both combined and individual queries gave the same correct results and created the same SQL. Here is a link to use LINQPad with Entity Framework . One of the differences may be in using the repository template, I do not use it. I would recommend testing with the first query to see that SQL is generated. After executing the query, LINQPad has an SQL tab that can help troubleshoot what happens when you look at the generated SQL.

If you are still having problems with the combined LINQ statement, a good next step would be to try an Entity Framework object without repository objects. If this request works, there might be something wrong with your repository objects.

 // Guessing that Metric and VolumeAdditive are the EF Entities // LINQPad database dropdown sets the context so they were not set it in these samples var metrics = from m in Metric where !(from v in VolumeAdditive where v.AdditivesID == AdditivesID select v.MetricID).Contains(m.ID) select m; 

To find out what the problem is, I would use a MetricRepository with a VolumeAdditive EF object.

 var metrics = from m in work.MetricRepository.All where !(from v in VolumeAdditive where v.AdditivesID == AdditivesID select v.MetricID).Contains(m.ID) select m; 

Then I would switch them to using the Metric EF object using VolumeAdditiveRepository.

 var metrics = from m in Metric where !(from v in work.VolumeAdditiveRepository.All where v.AdditivesID == AdditivesID select v.MetricID).Contains(m.ID) select m; 

Based on the generated SQL and what queries work, I think this should help you in the right direction. This is based on removing parts of the problem until it works. Then add them back until they crash to indicate where the problem is. These steps should be performed using small incremental changes to minimize problem space.

---

Update:

Based on the new information, try sharing the new information with the new questions that we need to answer.

Perhaps the LINQ expression cannot determine what to do with work._context.VolumeAdditive in the where clause. So let's test this theory using the following. This sets the context for a single variable instead of using work._context.

 var _context = work._context; var query = from m in _context.Metric where !(from v in _context.VolumeAdditive where v.AdditivesID == AdditivesID select v.MetricID).Contains(m.ID) select m; 

Perhaps using the let statement to determine MetricID might solve this problem.

 var metrics = from m in work.MetricRepository.All let volumes = from v in work.VolumeAdditiveRepository.All where v.AdditivesID == AdditivesID select v.MetricID where !volumes.Contains(m.ID) select m; 

Based on the results of these tests and mixing and comparing the previous 3 tests / questions, we should come closer to the answer. When I encounter such problems, I try to ask my questions with verifiable answers. Basically, I am trying to use the Scientific method to narrow the problem, to find a solution.