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!!

Answer 1

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();

Answer 2

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();