For translating SQL to LINQ query comprehension:
- Translate subselects as separately declared variables unless they reference columns outside the subselect, in which case use parentheses to create a sub-query.
- Translate each clause in LINQ clause order, translating monadic and aggregate operators (
DISTINCT
,TOP
,MIN
,MAX
etc) into functions applied to the whole LINQ query. - Use table aliases as range variables. Use column aliases as anonymous type field names.
- Use anonymous types (
new {
…}
) for multiple columns (e.g. ingroupby
). - Use
First().field
to get non-key values from thegroupby
aggregate range variable. - When using EF or EF Core, translate
JOIN
clauses into navigation properties possibly using.Include()
. - Otherwise
JOIN
clauses that are multipleAND
ed equality tests between the two tables should be translated into anonymous objects on each side ofequals
. JOIN
conditions that aren’t all equality tests withAND
must be handled usingwhere
clauses outside the join, or with cross product (from
…from
…) and thenwhere
. If you are doingLEFT JOIN
, add a lambdaWhere
clause between the join range variable and theDefaultIfEmpty()
call.LEFT JOIN
is simulated by usinginto
joinvariable and doing anotherfrom
the joinvariable followed by.DefaultIfEmpty()
.- Translate multiple tables in the
FROM
clause into multiplefrom
clauses. - Translate
FROM T1 CROSS APPLY T2
into twofrom
clauses, one forT1
and one forT2
. - Translate
FROM T1 OUTER APPLY T2
into twofrom
clauses, one forT1
and one forT2
, but add.DefaultIfEmpty()
toT2
. - Replace
COALESCE
with the conditional operator (?:
)and anull
test. - Translate
IN
to.Contains()
andNOT IN
to!
…Contains()
, using literal arrays or array variables for constant lists. - Translate x
BETWEEN
lowAND
high to low<=
x&&
x<=
high. - Translate
CASE
,ISNULL
andIIF
to the ternary conditional operator?:
. SELECT *
must be replaced with select range_variable or for joins, an anonymous object containing all the range variables.SELECT
columns must be replaced withselect new {
…}
creating an anonymous object with all the desired fields or expressions.- References to computed
SELECT
columns can be translated by repeating the expression or by usinglet
to name the expression before its first use. - Proper
FULL OUTER JOIN
must be handled with an extension method. - Translate
UNION
toConcat
unless both sub-queries areDISTINCT
, in which case you can translate toUnion
and leave off theDISTINCT
. - Translate aggregate queries that have no
GROUP BY
using a singletonGroupBy
: add.GroupBy(r => 1)
and then translate the aggregate functions in theSelect
. - Date Math and some other canonical functions can be accessed using
EF.Functions
to get an instance of theDbFunctions
class (EF Core),EntityFunctions
class (EF < 6) orDbFunctions
to access the static methods (EntityFramework 6.x). - Translate SQL
LIKE
expressions using (EF Core >= 2)EF.Functions.Like(column, pattern)
or (EF 6.x)DbFunctions.Like(column, pattern)
.
Applying these rules to your SQL query, you get:
var subrq = from r in Table_R
group r by r.Id into rg
select new { Id = rg.Key, cnt = rg.Count() };
var ansq = (from c in Table_C
join v in Table_V on c.Id equals v.Id
join r in subrq on c.Id equals r.Id into rj
from r in rj.DefaultIfEmpty()
where c.IdUser == "1234"
group new { c, v, r } by new { c.Id, c.Title, r.cnt } into cvrg
select new {
cvrg.Key.Title,
Nb_V2 = cvrg.Count(),
Nb_V1 = cvrg.Select(cvr => cvr.v.IdUser).Distinct().Count(),
Nb_R = (int?)cvrg.Key.cnt
}).Distinct();
The lambda translation is tricky, but the conversion of LEFT JOIN
to GroupJoin
…SelectMany
is what is needed:
var subr2 = Table_R.GroupBy(r => r.Id).Select(rg => new { Id = rg.Key, cnt = rg.Count() });
var ans2 = Table_C.Where(c => c.IdUser == "1234")
.Join(Table_V, c => c.Id, v => v.Id, (c, v) => new { c, v })
.GroupJoin(subr, cv => cv.c.Id, r => r.Id, (cv, rj) => new { cv.c, cv.v, rj })
.SelectMany(cvrj => cvrj.rj.DefaultIfEmpty(), (cvrj, r) => new { cvrj.c, cvrj.v, r })
.GroupBy(cvr => new { cvr.c.Id, cvr.c.Title, cvr.r.cnt })
.Select(cvrg => new { cvrg.Key.Title, Nb_V2 = cvrg.Count(), Nb_V1 = cvrg.Select(cvr => cvr.v.IdUser).Distinct().Count(), Nb_R = (int?)cvrg.Key.cnt });