Sum the fields whose tables are in join clause but are not in group clause

Question

I want to convert this query to Linq. I left joined BLTb to BMTb and CbTb. I did not use BLTb in group by clause. I can't convert this query to Linq because I can't sum BLTb.IsAprv. I just can sum the fields whose tables are in group by clause in linq.

Could someone please tell me how I can convert the below query to Linq.

Note:
"BLTb.BLCod" is not the PK of the table "BLTb".
"BMTb.Id" is not the PK of the table " BMTb"

SELECT     BMTb.BCod, CBTb.CmCod, 
CASE WHEN   
    SUM(CASE WHEN ISNULL(BMTb.IsAprv, 0) = 0 THEN 1 ELSE 0 END) +
    SUM(CASE WHEN BLTb.IsAprv = 0 THEN 1 ELSE 0 END)
    > 0 then 0 else 1 end AS IsAprv
FROM BMTb 
INNER JOIN CBTb ON BMTb.CBCod = CBTb.CBCod 
LEFT OUTER JOIN BLTb ON BMTb.Id = BLTb.BLCod
GROUP BY  BCod

Linq:

var BMTb = (from m in db.BMTbs.AsQueryable()
                            select new
                            {
                                isAprove=m.IsAprv==null?1:m.IsAprv==false?1:0,
                                m.CBCod,
                               m.IsAprv,
                               m.BCod,
                               m.Id
                            }
                            );
        var BLTb = (from l in db.BLTbs.AsQueryable()
                            select new
                            {
                                l.BMCod,
                               isAprove= l.IsAprv==false?1:0

                            }
                            );


        var AproveCB = (from m in BMTb.AsQueryable()
                           join c in db.CBTbs.AsQueryable() on m.CBCod equals c.CBCod
                           join l in BLTb.AsQueryable() on m.Id equals l.BMCod into lL
                           from lLeft in lL.DefaultIfEmpty()
                           group new { m, c } by new {m.BCod,c. CmCod, }
                           into grp
                           select new
                           {
                               sum1=grp.Sum(gp=>gp.m.isAprove)/*+grp.Sum(gp => gp.isAprove) */> 0?0:1,
                               BCod=grp.Key.BCod,
                               CmCod=grp.Key.CmCod,


                           }
                           );

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Jun 11 '20 at 16:23
Your grouping by `BCod` but your `SELECT` has a reference to `CBTb.CmCod` - how is that legal in SQL? — NetMage, Jun 11 '20 at 16:31

score 0 · Answer 1 · answered Jun 11 '20 at 16:34

Here is a straight forward translation of the SQL to Linq:

var ans = from b in BMTb
          join c in CBTb on b.CBCod equals c.CBCod
          join l in BLTb on b.Id equals l.Id into lj
          from l in lj.DefaultIfEmpty()
          group new { bIsAprv = b.IsAprv, c.CmCod, lIsAprv = l.IsAprv } by b.BCod into bclg
          select new {
              BCod = bclg.Key,
              bclg.First().CmCod,
              IsAprv = (bclg.Sum(bcl => bcl.bIsAprv == null ? 1 : 0)+bclg.Sum(bcl => bcl.lIsAprv == 0 ? 1 : 0) > 0) ? 0 : 1
          };

I used First() to pull out one CmCod from the groups, I don't believe the SQL is legal. I am also unsure how you can test BLTb.IsAprv when you are left joining with BLTb so it might be null.

Sum the fields whose tables are in join clause but are not in group clause

1 Answers1