Use Count in Linq returns different results than SQL query

Question

I use Linqer to convert SQL to LinQ but result are not the same My SQL query :

    Select ChuyenNganh.ChuyenNganhID,ChuyenNganh.TenChuyenNganh,SoLuong= count(BaiBao.ChuyenNganhID )
from BaiBao right join ChuyenNganh on ChuyenNganh.ChuyenNganhID = BaiBao.ChuyenNganhID
group by  ChuyenNganh.ChuyenNganhID, ChuyenNganh.TenChuyenNganh

Convert to Linq

var queryChuyenNganh = from t in myPhanLoaiTaiLieuDataContext.ChuyenNganhs
                                   join t0 in myPhanLoaiTaiLieuDataContext.BaiBaos on new { ChuyenNganhID = t.ChuyenNganhID } equals new { ChuyenNganhID = Convert.ToInt32(t0.ChuyenNganhID) } into t0_join
                                   from t0 in t0_join.DefaultIfEmpty()
                                   group t by new
                                   {
                                       t.ChuyenNganhID,
                                       t.TenChuyenNganh
                                   } into g
                                   select new
                                   {
                                       ChuyenNganhID = (System.Int32)g.Key.ChuyenNganhID,
                                       g.Key.TenChuyenNganh,

                                       SoLuong  =(Int32)g.Count()
                                   };

Result:

Linq

SQL

Who can fix it for me?

Answer 1

Most probably the SQL query can be rewritten in LINQ without group by but simple LINQ GroupJoin.

But the main issue with your conversion is that SQL COUNT(expr) does not count NULL values, and there is no direct LINQ equivalent, so either conditional Count or Sum is needed (I personally prefer the later because usually it translates to better SQL).

So, the minimum change needed in you query is

group t by new to group t0 by new

and SoLuong =(Int32)g.Count() to SoLuong = g.Sum(t0 => t0 != null ? 1 : 0)

P.S. As mentioned at the beginning, I would give a try to the following LINQ query:

var queryChuyenNganh = 
    from t in myPhanLoaiTaiLieuDataContext.ChuyenNganhs
    join bb in myPhanLoaiTaiLieuDataContext.BaiBaos
    on t.ChuyenNganhID equals bb.ChuyenNganhID into t_BaiBaos
    select new
    {
        t.ChuyenNganhID,
        t.TenChuyenNganh,
        SoLuong = t_BaiBaos.Count()
    }; 

Answer 2

The difference is that SQL counts non-null values in BaiBao.ChuyenNganhID, a column in the outer join table, while LINQ counts all records.

You need to make LINQ count non-null values as well:

var queryChuyenNganh = from t in myPhanLoaiTaiLieuDataContext.ChuyenNganhs
   join t0 in myPhanLoaiTaiLieuDataContext.BaiBaos
       on new { t.ChuyenNganhID } equals new { ChuyenNganhID = Convert.ToInt32(t0.ChuyenNganhID) } into t0_join
   from t0 in t0_join.DefaultIfEmpty()
   group new {T=t, NonNull=t0.ChuyenNganhID != null} by new
   {
       t.T.ChuyenNganhID,
       t.T.TenChuyenNganh
   } into g
   select new
   {
       ChuyenNganhID = (System.Int32)g.Key.ChuyenNganhID,
       g.Key.TenChuyenNganh,
       SoLuong  =(Int32)g.Count(x => x.NonNull)
   };