Can this SQL be translated to LINQ?

Question

Hi, I am working with a quite tricky SQL-Query that I would like to translate to LINQ.

Do you think it is possible?

WITH ConditionalChecks AS (
    SELECT c.ItemId FROM ConditionalProperties c, Properties p
    WHERE c.PropertyId = p.Id AND c.IsChecked = 1 AND (

     (p.SystemName = 'eatable') OR
     (p.SystemName = 'diy')

    )
),

ConditionalCount AS (
    SELECT ItemId, Count(*) AS NumTrue FROM ConditionalChecks
    GROUP BY ItemId
),

ItemResult AS (
    SELECT * FROM ConditionalCount c, Items i
    WHERE c.ItemId = i.Id
)

SELECT * FROM ItemResult
WHERE NumTrue = 2

Any hints are appreciated!

Answer 1

I think something like this should work

Select * FROM (
    Select c.ItemID, Count(*) As NumTrue
    FROM ConditionalProperties c, Properties p
    WHERE c.PropertyId = p.Id AND c.IsChecked = 1 AND (
    (p.SystemName = 'eatable') OR
    (p.SystemName = 'diy')
    GROUP BY ItemID
) AS ConditionalCount 
INNER JOIN Items AS I
ON  ConditionalCount.ItemID = I.id
WHERE ConditionalCount.NumTrue = 2

This is SQL, don't know why I thought you were doing LINQ, and wanted SQL, but this is a much simpler form of the SQL that should do the exact same thing as your multiple queries.

Answer 2

LINQ makes it easy to aggregate queries like this.

var conditionalChecksQuery = 
    from c in db.ConditionalProperties
    where c.IsChecked == 1  // or 'true', if boolean
    join p in db.Properties on c.PropertyId equals p.Id
    where p.SystemName == "eatable" || p.SystemName == "diy"
    select c.ItemId;

var conditionalCountQuery = 
    from c in conditionalChecksQuery
    group c by c.ItemId into cGrouped
    select new { ItemId = cGrouped.Key, NumTrue = cGrouped.Count() };

var itemResultQuery = 
    from c in conditionalCountQuery
    join i in db.Items on c.ItemId equals i.Id
    select new { Item = i, NumTrue = c.NumTrue };

var finalQuery =
    from result in itemResultQuery
    where result.NumTrue == 2
    select result;