CODEWITHSUNDEEP
c#sql-serverlinq
Pedro
Pedro

Reputation: 25

Count each columns in linq with left join c#

How can I do this query using linq c#

SELECT count(tp.column1), count(tf.column2) FROM table1 tp
left join table2 tf on tf.column1= tp.column2

Result expected:

column1 column2
39685   12

I have the following code:

var query = (from t in  table1
            from t2 in  table2.Where (t2=> t2.column2 == t1.column1).DefaultIfEmpty()
            select new {
                x =  t1.column1,
                b = t2.column2
            });

How can I count those columns like in sql?

Upvotes: 1

Views: 85

Answers (1)

StriplingWarrior
StriplingWarrior

Reputation: 156728

You'll need to fool your LINQ provider into thinking that it's got a collection of collections to deal with, so that your Count() is evaluated as part of the SQL rather than invoking a round-trip all by itself. You can do this by "grouping" the values all into one big group.

var query = 
    from t in table1
    let t2 = table2.Where (t2=> t2.column1 == t2.column2)
    group new{t, t2} by 0 into g
    select new{
        column1 = g.Count()
        column2 = g.SelectMany(e => e.t2).Count()
    }

Upvotes: 2

Related Questions