CODEWITHSUNDEEP
c#entity-frameworkentity-framework-6linq-to-entities
Ralf
Ralf

Reputation: 548

How can I use linq-to-sql with references?

I use Entity Framework 6, and derived my model from the database. I did not set the option to "include foreign keys columns".

I can add new entities to this model, to set the foreign key references I use a reference to the other object, like this:

client.Country = country;

When trying to query the model using references I encounter a problem:

ctx.Client.Where(c => c.Country == country);

results in an error "Unable to create constant value of type "country type". Only primitive types are supported".

Using the numeric id of the foreign key

ctx.Client.Where(c => c.id_Country == country.idCountry);

cannot work, as I chose not to include the foreign key columns.

I found an ugly workaround by loading it all to memory and filtering there, using the reference

List<Client> clients = ctx.Clients.ToList();
clients.Where(c => c.Country == country);

but that is not very elegant.

Any idea? Including the foreign keys is easiest, obviosuly, but the model looks so much cleaner without.

Thanks in advance!

Upvotes: 1

Views: 271

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205579

You can use [reference property].[key property] for that, e.g.

c => c.Country.id_Country == country.idCountry

EF is smart enough to recognize it as FK property accessor inside LINQ to Entities queries and translate it to the FK column name.

In other words, in L2E query c.Country.id_Country is the same as c.id_Country FK property if it existed.

Upvotes: 2

Related Questions