IN and NOT IN with Linq to Entities (EF4.0)

Question

This has been ruining my life for a few days now, time to ask...

I am using Entity Framework 4.0 for my app.

A Location (such as a house or office) has one or more facilities (like a bathroom, bedroom, snooker table etc..)

I want to display a checkbox list on the location page, with a checkbox list of facilities, with the ones checked that the location currently has.

My View Model for the facilities goes like this...

public class FacilityViewItem
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool Checked { get; set; }
}

So when im passing the Location View Model to the UI, i want to pass a List<T> of facilities where T is of type FacilityViewItem.

To get the facilities that the location already has is simple - i make a query using Location.Facilities which returns an EntityCollection where T is of type Facility. This is because Facilities is a navigation property....

var facs = from f in location.Facilities
select new FacilityViewItem()
{
    Id = f.FacilityId,
    Name = f.Name,
    Checked = true
};

So here is where my problem lies - i want the rest of the facilities, the ones that the Location does not have.

I have tried using Except() and Any() and Contains() but i get the same error.

Examples of queries that do not work...

var restOfFacilities = from f in ctx.Facilities
    where !hasFacilities.Contains(f)
    select new FacilityViewItem()
        {
            Id = f.FacilityId,
            Name = f.Name
        };

var restOfFacilities = ctx.Facilities.Except(facilitiesThatLocationHas);

var notFacs = from e in ctx.Facilities
where !hasFacilities.Any(m => m.FacilityId == e.FacilityId)
    select new FacilityViewItem()
        {
            Id = e.FacilityId,
            Name = e.Name
        };

And the error i get with every implementation...

System.NotSupportedException was unhandled Message=Unable to create a constant value of type 'Chapter2ConsoleApp.Facility'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

What am i overlooking here?

Answer 1

ironically enough i solved it in a matter of hours after i posted the question on here, after days of suffering.

The error is basically saying 'i dont know how to calculate what items are not included by comparing strongly typed objects. Give me a list of Ints or some simple types, and i can take care of it'.

So, first you need to get a list of the primary keys, then use that in the contains clause...

//get the primary key ids...
var hasFacilityIds = from f in hasFacilities
    select f.FacilityId;

//now use them in the contains clause...
var restOfFacilities = from f in ctx.Facilities
    where !hasFacilityIds.Contains(f.FacilityId)
        select new FacilityViewItem()
            {
                Id = f.FacilityId,
                Name = f.Name
            };

Answer 2

The first query seems fine, but you need to compare the Ids:

var restOfFacilities = from f in ctx.Facilities
                       where !facs.Select(fac => fac.Id).Contains(f.Id)
                       select f;

Answer 3

I wanna see what's hasFacilities, anyway, as L2E shows, "Only primitive types ('such as Int32, String, and Guid') are supported in this context", so I suppose you must retrieve first the data and put into a collection of FacilityViewItem.

var restOfFacilities = ctx
    .Facilities
    .Where(f => !hasFacilities.Contains(f))
    .Select(f => new { f.FacilityId, f.Name })
    .ToList()
    .Select(f => new FacilityViewItem {
        Id = f.FacilityId,
        Name = f.Name
    });

var notFacs = ctx
    .Facilities
    .Where(e => !hasFacilities.Any(m => m.FacilityId == e.FacilityId))
    .Select(e => new { e.FacilityId, e.Name })
    .ToList()
    .Select(e => new FacilityViewItem {
        Id = e.FacilityId,
        Name = e.Name
    });

hope it helps