I'm little bit new to Entity Framework Core v2.1, and my problem is as below.
In my case there is a table called
ServiceCarrier like this:
ServiceId CarrierId ------------------- 1 1 2 1 4 1 1 2 2 2 5 2 1 20028 2 20028 5 20028
By using this SQL query, I was able to get the result set needed:
SELECT serviceid FROM T GROUP BY serviceid WHERE carrierid IN (1, 20028) HAVING count(*) = (SELECT COUNT(DISTINCT carrierid) FROM T WHERE carrierid IN (1, 20028));
ServiceId --------- 1 2
And I want to convert it to Entity Framework Core 2.1.
Appreciate your help in advance.
Your own solution is ok, but the problem is that condition like
.Where(sc => carriers.Any(c => c.CarrierId == sc.CarrierId))
carriers is in-memory collection leads to client evaluation, so while it will produce a correct result, the performance won't be good.
A better way is to prepare a collection of ids and use
Contains method, which is the typical way to implement the LINQ equivalent of SQL
IN operator in EF (Core):
var carrierIds = carriers.Select(e => e.CarrierId); var result = db.ServiceCarriers .Where(sc => carrierIds.Contains(sc.CarrierId)) .GroupBy(sc => sc.ServiceId) .Where(scg => scg.Count() == db.ServiceCarriers .Where(sc => carrierIds.Contains(sc.CarrierId)) .Select(sc => sc.CarrierId) .Distinct() .Count() ) .Select(scg => scg.Key) .ToList();
The resulting SQL query is almost the same as the original:
SELECT [sc].[ServiceId] AS [Key] FROM [ServiceCarriers] AS [sc] WHERE [sc].[CarrierId] IN (1, 20028) GROUP BY [sc].[ServiceId] HAVING COUNT(*) = ( SELECT COUNT(*) FROM ( SELECT DISTINCT [sc0].[CarrierId] FROM [ServiceCarriers] AS [sc0] WHERE [sc0].[CarrierId] IN (1, 20028) ) AS [t] )
I was able to make a solution for that above question. My solution is shown below. And if anyone found better solution rather than this solution please share.
_dbContext.ServiceCarriers .Where(sc => carriers.Any(c => c.CarrierId == sc.CarrierId)) .GroupBy(scg => scg.Service) .Where(sc1 => sc1.Count() == _dbContext.ServiceCarriers .Where(scc => carriers.Any(c => c.CarrierId == scc.CarrierId)) .Select(t => t.CarrierId) .Distinct() .Count() ).Select(sr => sr.Key) .ToList();