Linq C # - combining multiple conditions errors
I am trying to make a request with 3 conditions per link. But I am getting an error. It sql server
works fine, but when I try to convert it to linq
it gives me an erro.
You can see below the error and request.
Request:
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 a filter with a string as well, so any idea how I can do this.
Mistake:
Type arguments for the 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 decommissioned.
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 I can fix this problem?
Thank!!
source to share
While it is not clear to me why the include connection CountryId
is causing this error, you can work around the problem by matching CountryId
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();
source to share
It seems that your selector in the 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 }
)
This expression will work fine in LinqToObjects, but it is not valid in LinqToEntities.
You have two options.
You can either mark the returned 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 return an anonymously typed object that contains two properties (the objects you are interested in).
Note. I have reworked the queries somewhat. I noticed that you are checking if Favorites == null. This will already take care of you with a join that maps to a SQL 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 need to explicitly list all returned fields. For this approach, you will either need to name the properties carefully, or just create a DTO (Data Transfer Object) to hold the return values that interest you.
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();
source to share