Question
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));
Results:
ServiceId
---------
1
2
And I want to convert it to Entity Framework Core 2.1.
Appreciate your help in advance.
Accepted Answer
Your own solution is ok, but the problem is that condition like
.Where(sc => carriers.Any(c => c.CarrierId == sc.CarrierId))
where 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]
)
Popular Answer
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();
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow