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

Question

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

This block of code, works perfectly:

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 take a good look, you can see I use the variable volumes inside this snippet, in the where condition. If I copy the contents of this variable and paste it inside the metrics variable, leading to the code below, it raises the 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 variable substitution cause such error?! Am I doing (certainly) something wrong?
Thank you!

---

UPDATE:

Actually, I find out that the Repository Pattern or DbContext seems to be the problem, as @jhamm pointed out. The snippet below doesn't work either:

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 snippet below works. I just took the context out of the UnitOfWork class, though 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 stuff! Wasn't it supposed to work as expected?!

Answer 1

I used LINQPad to use my EF Database First model on a similar type of query. Both the combined and separate queries gave the same correct results and generated the same SQL. Here is a link on how to use LINQPad with Entity Framework. One difference could be the use of the Repository Pattern, I am not using one. I would recommend testing with the first query to see what SQL is generated. After you run a query, LINQPad has a SQL tab that may help troubleshoot what is going on by looking at the generated SQL.

If you are still having trouble with the combined LINQ statement, a good next step would be to try the Entity Framework object without the Repository objects. If this query works, there may 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 where the issue lies, I would next use the MetricRepository with the 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 use the Metric EF object with the 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 which queries work, I think this should help point you in the right direction. This is based on removing parts of the problem until it works. Then adding them back in until they break to indicate where the issue is. These steps should be done using small incremental changes to minimize the problem space.

---

Update:

Based on the new information, lets try to divide the new information into new questions that we need to answer.

Maybe the LINQ expression is not able to figure out what to do with the work._context.VolumeAdditive in the where clause. So lets test this theory by using the following. This sets the context to 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;

Maybe using a let statement to define the MetricID's could resolve this issue.

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 matching the previous 3 tests/questions, we should be getting closer to the answer. When I come up against issues like this, I try to ask my self questions with verifiable answers. Basically, I try to use the Scientific Method to narrow down the issue to find a resolution.