Translate SQL query into Entity Framework Core 2.1

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.

Ivan Stoev · Accepted Answer · 2018-07-09 07:58:07Z

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]
)

Dharman · Accepted Answer · 2020-02-18 17:11:28Z

1

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

edited Feb 18, 2020 at 17:11

Dharman♦

34k27 gold badges106 silver badges158 bronze badges

answered Jul 8, 2018 at 18:17

SachithR

2310 bronze badges

1 Comment

SachithR Over a year ago

@SelvaTS to me yes.do you see any bug on this one ? Carrier list should be pased to the sql

Collectives™ on Stack Overflow

Translate SQL query into Entity Framework Core 2.1

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related