Linq Join and aggregate

Question

Kindly help me implement this sql query that contains Joining and conditional COUNT and SUM in Linq:

   SELECT  
            COUNT(distinct Spool.ISODwg+'-'+Spool.SpoolNo ) as All_Spool,
            SUM(case when (JointFMC.SBNM='AG' and JointFMC.SF='S') then JointFMC.Size else 0 end) as All_DI,
            COUNT(distinct case when (Spool.ReleaseNo is not null and Spool.ReleaseNo!='' ) then Spool.ISODwg+'-'+Spool.SpoolNo else null end) as Release_Spool,
            SUM(case when (Spool.ReleaseNo is not null and Spool.ReleaseNo!='') and (JointFMC.SBNM='AG' and JointFMC.SF='S') then JointFMC.Size else 0 end) as Release_DI,
            COUNT(distinct case when (JointFMC.RT_Status='Result Needed' or  JointFMC.RT_Status='App. Needed' ) and (JointFMC.SBNM='AG' and JointFMC.SF='S') then Spool.ISODwg+'-'+Spool.SpoolNo else null end) as BL_Spool,
            SUM(case when (JointFMC.RT_Status='Result Needed' or  JointFMC.RT_Status='App. Needed' ) and (JointFMC.SBNM='AG' and JointFMC.SF='S') then JointFMC.Size else 0 end) as BL_DI

    FROM Spool LEFT JOIN JointFMC ON (Spool.ISODwg = JointFMC.ISODwg and Spool.SpoolNo = JointFMC.SpoolNo)

Spool Class (partial)

public class Spool : ModelBase
{
    #region Properties-------------------------------------------------------------------------------
    //ISODwgNo
    private string _iSODwgNo;
    public string ISODwgNo
    {
        get { return _iSODwgNo; }
        set
        {
            if (_iSODwgNo != value)
            {
                _iSODwgNo = value;
                ValidateProperty("ISODwgNo", value);
                OnPropertyChanged("ISODwgNo");
            }
        }
    }

    //ISODwg
    private string _iSODwg;
    public string ISODwg
    {
        get { return _iSODwg; }
        set
        {
            if (_iSODwg != value)
            {
                _iSODwg = value;
                ValidateProperty("ISODwg", value);
                OnPropertyChanged("ISODwg");
            }
        }
    }

    //SpoolNo
    private string _spoolNo;
    public string SpoolNo
    {
        get { return _spoolNo; }
        set
        {
            if (_spoolNo != value)
            {
                _spoolNo = value;
                ValidateProperty("SpoolNo", value);
                OnPropertyChanged("SpoolNo");
            }
        }
    }


    //Type
    private string _type;
    public string Type
    {
        get { return _type; }
        set
        {
            if (_type != value)
            {
                _type = value;
                ValidateProperty("Type", value);
                OnPropertyChanged("Type");
            }
        }
    }


    //Area
    private string _area;
    public string Area
    {
        get { return _area; }
        set
        {
            if (_area != value)
            {
                _area = value;
                ValidateProperty("Area", value);
                OnPropertyChanged("Area");
            }
        }
    }
}

JointFMC Class (partial)

public class JointFMC : ModelBase
{
    #region Properties------------------------------------------------------
    //SBNM
    private string _sBNM;
    public string SBNM
    {
        get { return _sBNM; }
        set
        {
            if (_sBNM != value)
            {
                _sBNM = value;
                ValidateProperty("SBNM", value);
                OnPropertyChanged("SBNM");
            }
        }
    }


    //ISODwg
    private string _iSODwg;
    public string ISODwg
    {
        get { return _iSODwg; }
        set
        {
            if (_iSODwg != value)
            {
                _iSODwg = value;
                ValidateProperty("ISODwg", value);
                OnPropertyChanged("ISODwg");
            }
        }
    }


    //JointNo
    private string _jointNo;
    public string JointNo
    {
        get { return _jointNo; }
        set
        {
            if (_jointNo != value)
            {
                _jointNo = value;
                ValidateProperty("JointNo", value);
                OnPropertyChanged("JointNo");
            }
        }
    }



    //SpoolNo
    private string _spoolNo;
    public string SpoolNo
    {
        get { return _spoolNo; }
        set
        {
            if (_spoolNo != value)
            {
                _spoolNo = value;
                ValidateProperty("SpoolNo", value);
                OnPropertyChanged("SpoolNo");
            }
        }
    }


    //Size
    private double? _size;
    public double? Size
    {
        get { return _size; }
        set
        {
            if (_size != value)
            {
                _size = value;
                ValidateProperty("Size", value);
                OnPropertyChanged("Size");
            }
        }
    }


    //SF
    private string _sF;
    public string SF
    {
        get { return _sF; }
        set
        {
            if (_sF != value)
            {
                _sF = value;
                ValidateProperty("SF", value);
                OnPropertyChanged("SF");
            }
        }
    }


    //WeldRpNo
    private string _weldRpNo;
    public string WeldRpNo
    {
        get { return _weldRpNo; }
        set
        {
            if (_weldRpNo != value)
            {
                _weldRpNo = value;
                ValidateProperty("WeldRpNo", value);
                OnPropertyChanged("WeldRpNo");
            }
        }
    }


    //RT_Status
    private string _rT_Status;
    public string RT_Status
    {
        get { return _rT_Status; }
        set
        {
            if (_rT_Status != value)
            {
                _rT_Status = value;
                ValidateProperty("RT_Status", value);
                OnPropertyChanged("RT_Status");
            }
        }
    }


    #endregion

}

Regards

Here is an example of how to do it in one query: http://stackoverflow.com/questions/1597181/linq-to-sql-how-to-aggregate-without-a-group-by — Łukasz Trzewik, Sep 29 '16 at 07:26
@ user449689: i tried many ideas with no use and the last thing i will try is make it 2 queries first one for grouping and the second for final result — Hussein, Sep 29 '16 at 08:30
Could you at least post the code of the two classes involved, so we can use VS intellisense (not willing to try writing all that in notepad). — Ivan Stoev, Sep 29 '16 at 11:55

score 2 · Accepted Answer · answered Oct 01 '16 at 10:14

Here is the direct LINQ translation:

var query =
    from Spool in db.Spool
    join JointFMC in db.JointFMC
    on new { Spool.ISODwg, Spool.SpoolNo }
    equals new { JointFMC.ISODwg, JointFMC.SpoolNo } into JointFMCJoin
    from JointFMC in JointFMCJoin.DefaultIfEmpty()
    group new { Spool, JointFMC } by 1 into g
    select new
    {
        All_Spool = g
            .Select(e => e.Spool.ISODwg + "-" + e.Spool.SpoolNo).Distinct().Count(),
        All_DI = g.Sum(e => e.JointFMC.SBNM == "AG" && e.JointFMC.SF == "S" ?
            e.JointFMC.Size : 0),
        Release_Spool = g.Where(e => !string.IsNullOrEmpty(e.Spool.ReleaseNo))
            .Select(e => e.Spool.ISODwg + "-" + e.Spool.SpoolNo).Distinct().Count(),
        Release_DI = g.Sum(e => !string.IsNullOrEmpty(e.Spool.ReleaseNo) && e.JointFMC.SBNM == "AG" && e.JointFMC.SF == "S" ?
            e.JointFMC.Size : 0),
        BL_Spool = g.Where(e => (e.JointFMC.RT_Status == "Result Needed" || e.JointFMC.RT_Status == "App. Needed") && e.JointFMC.SBNM == "AG" && e.JointFMC.SF == "S")
            .Select(e => e.Spool.ISODwg + "-" + e.Spool.SpoolNo).Distinct().Count(),
        BL_DI = g.Sum(e => (e.JointFMC.RT_Status == "Result Needed" || e.JointFMC.RT_Status == "App. Needed") && e.JointFMC.SBNM == "AG" && e.JointFMC.SF == "S" ?
            e.JointFMC.Size : 0),
    };

Note that LINQ does not have a good equivalent of the SQL COUNT(DISTINCT ..) construct, so the generated SQL query from the above LINQ query will be much more complicated (and probably slower) than the original. In such scenarios it might be better to not use LINQ and execute a raw SQL query if the ORM tool supports that.

Linq Join and aggregate

1 Answers1