Linq c# - Join Multiple Conditions Error

Question

I'm trying to make a query with a 3 conditions on a join. But I get an error. In the sql server it's working perfectly, but when I try to convert it to linq it's giving me an erro.

You can take a look below the error and the query.

Query:

var temp = _context .FavouriteVol .Join(_context.Favourites, fv => new { fv.EntityId, fv.CountryId, fv.EType }, f => new { f.EntityId, f.CountryId, f.EType }, (fv, f) => new { Favourites = f, FavouriteVol = fv }) .Where(u => u.Favourites.userId == userId) .Select(f => f.Favourites) .ToList();

Note: EntityId (int), CountryId (string), and EType (int)`. The problem is with the string. but I need filter also with the string, so any idea how can I do it.

Error:

The type arguments for method 'System.Linq.Queryable.Join(System.Linq.IQueryable, System.Collections.Generic.IEnumerable, System.Linq.Expressions.Expression>, System.Linq.Expressions.Expression>, System.Linq.Expressions.Expression>)' cannot be inferred from the usage.

Sql:

SELECT * FROM db.FavouriteVol FV INNER JOIN db.Favourite F On F.EType = FV.EType and F.CountryId = FV.CountryId and F.EType = FV.EType WHERE F.userId = 5

Any idea how can I fix this problem?

Thanks!!

What are the exact types of the EntityId, CountryId, and EType properties? — Gert Arnold
– Gert Arnold, Commented Apr 22, 2015 at 11:30
@GertArnold EntityId (int), CountryId (string), and EType (int) any idea how to fix it? I can not change the types on the database. — gon250
– gon250, Commented Apr 22, 2015 at 11:33
@GertArnold Well the problem is with the string. Do you know any solution for this? :) — gon250
– gon250, Commented Apr 22, 2015 at 11:43
Well I suppose it because if I remove the string, it's working ok. (=> new { fv.EntityId, fv.EType }, f => new { f.EntityId, f.EType },) I don't know why :s — gon250
– gon250, Commented Apr 22, 2015 at 11:48
Both country columns are string(eg. 'IRL') on the database. I know it's a little bit confuse but I didn't create this app. I just trying to fix a problem on it and I need it. I have no idea why doesn't work the condition with the string . Any suggestion or something to try to get it working? — gon250
– gon250, Commented Apr 22, 2015 at 11:57

gon250 · Accepted Answer · 2015-04-22 13:42:45Z

Although it's not clear to me why the join including CountryId causes this error, you can work around the problem by matching the CountryIds separately:

var temp = _context .FavouriteVol .Join(_context.Favourites, fv => new { fv.EntityId, fv.EType }, f => new { f.EntityId, f.EType }, (fv, f) => new { Favourites = f, FavouriteVol = fv }) .Where(u => u.Favourites.userId == userId && u.Favourites.CountryId == u.FavouriteVol.CountryId) .Select(f => f.Favourites) .ToList();

Let me know if you find why we can not set the country (string) on the join and do it on the where. Thanks for your time!

Eniola · Accepted Answer · 2015-04-22 12:25:46Z

It seems your selector in your Join clause is the problem.

.Join(_context.Favourites, fv => new { fv.EntityId, fv.CountryId, fv.EType }, f => new { f.EntityId, f.CountryId, f.EType }, (fv, f) => new { Favourites = f, FavouriteVol = fv } )

In LinqToObjects, that expression will work fine but it is invalid in LinqToEntities.

You have two options.

You can either Mark the return sets as Enumerable, in which case all Entities from the sets will be returned to the client and filtered there (not good for performance).
You will get back an anonymously typed object that contains two properties (the entities you're interested in).

Note: I have reworked the queries somewhat. I noticed you are testing for whether Favourites == null. That is already taken care of for you with a Join which maps to SQL's INNER JOIN.

var temp = _context .FavouriteVol .AsEnumerable() .Join( _context.Favourites .Where(u => u.userId == userId) .AsEnumerable(), fv => new { fv.EntityId, fv.CountryId, fv.EType }, f => new { f.EntityId, f.CountryId, f.EType }, (fv, f) => new { FavouriteVol = fv, Favourites = f } ) .ToList();

Or you will have to specify all returned fields explicitly. For this approach, you will need to either name the properties carefully or just create a DTO (Data Transfer Object) to hold the return values you are interested in.

var temp = _context .FavouriteVol .Join( _context.Favourites .Where(u => u.userId == userId), fv => new { fv.EntityId, fv.CountryId, fv.EType }, f => new { f.EntityId, f.CountryId, f.EType }, (fv, f) => new { EntityId = fv.EntityId, CountryId = fv.CountryId, EType = fv.EType, //<other fv properties>, UserId = f.UserId, //<other f properties> } ) .ToList();

"It seems your selector in your Join clause is the problem". Then why does a join without the CountryId work without any problems?
To be honest, I generally do not use anonymous types in my joins or projections. I tend to define my DTO's ahead of time because I have been burnt by similarly named anonymous type fields in different locations in the same assembly that have different underlying types. I also do a lot of joins that involve fields of different types int, string and DateTime all mixed in. Perhaps if you give me the schemas for FavouriteVol and Favourites, I can create a sample and justify my approach.

Collectives™ on Stack Overflow

Linq c# - Join Multiple Conditions Error

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related