none
Converting from SQL to LINQ RRS feed

  • Question

  • Hi,
    I need some help translating this query to LINQ. 

    SELECT

     

    [t1].[Description] As [ModelNumber],
            (SELECT [t2].[Value] FROM [dbo].[Client_ProductValues] AS [t2] 
             INNER JOIN [dbo].[Client_ProductAttributes] AS [p0]
             ON [p0].[ClientProductAttributeID] = [t2].[ClientProductAttributeID]
             WHERE ([p0].[Field_Mapping] = 'Product_Name') AND ([t1].[ClientProductID] = [t2].[ClientProductID])
            ) AS [DisplayName], 
            (SELECT [t2].[Value] FROM [dbo].[Client_ProductValues] AS [t2]
             INNER JOIN [dbo].[Client_ProductAttributes] AS [p0]
             ON [p0].[ClientProductAttributeID] = [t2].[ClientProductAttributeID]
             WHERE ([p0].[Field_Mapping] = 'Model_Series_Name') AND ([t1].[ClientProductID] = [t2].[ClientProductID])
            ) AS [DisplayName]
    FROM
            (SELECT [t0].[ClientProductID], [t0].[Description], [t0].[OrgID]
              FROM [dbo].[Client_Products] AS [t0]
              GROUP BY [t0].[ClientProductID], [t0].[Description], [t0].[OrgID]
            ) AS [t1]

    Your help is always appreciated.

    thanks,


    kashif
    Thursday, November 19, 2009 1:38 PM

Answers

  • Thank you for your reply.  I figured that there is no way around hard coding either by name or id, so i choose id.  here is what i come up with to solve it for now.  i can store the attributes array to a config file or something, let's see what my need becomes...

    Table

     

    <Client_ProductValue> pValue = context.GetTable<Client_ProductValue>();
    var r = pValue.Where(p => p.Client_ProductAttribute.OrgID == pOrgID).GroupBy(p => p.ClientProductID)
         .Select(ge =>
    new
         {
            Product_ID = ge.Key,
            Product_Name = ge.Where(a => a.ClientProductAttributeID == nAttributes[0]).Select(a => a.Value).First(),
            Model_Year = ge.Where(a => a.ClientProductAttributeID == nAttributes[1]).Select(a => a.Value).First(),
            Model_Number = ge.Where(a => a.ClientProductAttributeID == nAttributes[2]).Select(a => a.Value).First(),
            Series_Name = ge.Where(a => a.ClientProductAttributeID == nAttributes[3]).Select(a => a.Value).First(),
            Doors = ge.Where(a => a.ClientProductAttributeID == nAttributes[4]).Select(a => a.Value).First(),
            Cylinders = ge.Where(a => a.ClientProductAttributeID == nAttributes[5]).Select(a => a.Value).First(),
            Color = ge.Where(a => a.ClientProductAttributeID == nAttributes[6]).Select(a => a.Value).First(),
            Fuel_Type = ge.Where(a => a.ClientProductAttributeID == nAttributes[7]).Select(a => a.Value).First(),
            Plant_Name = ge.Where(a => a.ClientProductAttributeID == nAttributes[8]).Select(a => a.Value).First()
          });

    System.Data.Common.
    DbConnection scon = new System.Data.SqlClient.SqlConnection(context.Connection.ConnectionString);
    scon.Open();
    var scmd = context.GetCommand(r as IQueryable);
    scmd.Connection = scon;
    System.Data.Common.
    DbDataAdapter dAdap = new System.Data.SqlClient.SqlDataAdapter();
    dAdap.SelectCommand = scmd;
    dAdap.Fill(nDataSet);

    thanks,


    kashif
    • Edited by Kashif Pervaiz Friday, November 20, 2009 11:48 AM Lambda First Method to get single record
    • Marked as answer by Kashif Pervaiz Friday, November 20, 2009 6:21 PM
    Friday, November 20, 2009 11:16 AM

All replies

  • Somebody?  Anybody?  Please advice.

    Thanks,
    kashif
    Thursday, November 19, 2009 11:24 PM
  • Anyway, this should give you something fairly close to your sample SQL:
    from cp in dc.ClientProducts
    group cp by new { cp.ClientProductID, cp.Description, cp.OrgID } into cg
    select new
    {
      ModelNumber = cg.Key.Description,
      DisplayName1 = (
        from cpv in dc.ClientProductValues
        join cpa in dc.ClientProductAttributes on cpv.ClientProductAttributeID equals cpa.ClientProductAttributeID
        where cpv.ClientProductID == cpg.Key.ClientProductID
          && cpa.FieldMapping == "Product_Name"
        select cpv.Value
        ).Min(),
      DisplayName2 = (
        from cpv in dc.ClientProductValues
        join cpa in dc.ClientProductAttributes on cpv.ClientProductAttributeID equals cpa.ClientProductAttributeID
        where cpv.ClientProductID == cpg.Key.ClientProductID
          && cpa.FieldMapping == "Model_Series_Name"
        select cpv.Value
        ).Min()
    }

    ...alternatively you can replace the inline subqueries with left joins...

    from cp in dc.ClientProducts
    from pn in (
        from cpv in dc.ClientProductValues
        join cpa in dc.ClientProductAttributes on cpv.ClientProductAttributeID equals cpa.ClientProductAttributeID
        where cpv.ClientProductID == cpg.Key.ClientProductID
          && cpa.FieldMapping == "Product_Name"
        select cpv.Value
     ).DefaultIfEmpty()
    from mn in (
        from cpv in dc.ClientProductValues
        join cpa in dc.ClientProductAttributes on cpv.ClientProductAttributeID equals cpa.ClientProductAttributeID
        where cpv.ClientProductID == cpg.Key.ClientProductID
          && cpa.FieldMapping == "Model_Series_Name"
        select cpv.Value
     ).DefaultIfEmpty()
    group new { cp, pn, mn } by new { cp.ClientProductID, cp.Description, cp.OrgID } into cg
    select new
    {
      ModelNumber = cg.Key.cp.Description,
      DisplayName1 = cg.Key.pn.Value,
      DisplayName2 = cg.Key.mn.Value
    }
    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Friday, November 20, 2009 4:37 AM
    Answerer
  • Thank you for your reply.  I figured that there is no way around hard coding either by name or id, so i choose id.  here is what i come up with to solve it for now.  i can store the attributes array to a config file or something, let's see what my need becomes...

    Table

     

    <Client_ProductValue> pValue = context.GetTable<Client_ProductValue>();
    var r = pValue.Where(p => p.Client_ProductAttribute.OrgID == pOrgID).GroupBy(p => p.ClientProductID)
         .Select(ge =>
    new
         {
            Product_ID = ge.Key,
            Product_Name = ge.Where(a => a.ClientProductAttributeID == nAttributes[0]).Select(a => a.Value).First(),
            Model_Year = ge.Where(a => a.ClientProductAttributeID == nAttributes[1]).Select(a => a.Value).First(),
            Model_Number = ge.Where(a => a.ClientProductAttributeID == nAttributes[2]).Select(a => a.Value).First(),
            Series_Name = ge.Where(a => a.ClientProductAttributeID == nAttributes[3]).Select(a => a.Value).First(),
            Doors = ge.Where(a => a.ClientProductAttributeID == nAttributes[4]).Select(a => a.Value).First(),
            Cylinders = ge.Where(a => a.ClientProductAttributeID == nAttributes[5]).Select(a => a.Value).First(),
            Color = ge.Where(a => a.ClientProductAttributeID == nAttributes[6]).Select(a => a.Value).First(),
            Fuel_Type = ge.Where(a => a.ClientProductAttributeID == nAttributes[7]).Select(a => a.Value).First(),
            Plant_Name = ge.Where(a => a.ClientProductAttributeID == nAttributes[8]).Select(a => a.Value).First()
          });

    System.Data.Common.
    DbConnection scon = new System.Data.SqlClient.SqlConnection(context.Connection.ConnectionString);
    scon.Open();
    var scmd = context.GetCommand(r as IQueryable);
    scmd.Connection = scon;
    System.Data.Common.
    DbDataAdapter dAdap = new System.Data.SqlClient.SqlDataAdapter();
    dAdap.SelectCommand = scmd;
    dAdap.Fill(nDataSet);

    thanks,


    kashif
    • Edited by Kashif Pervaiz Friday, November 20, 2009 11:48 AM Lambda First Method to get single record
    • Marked as answer by Kashif Pervaiz Friday, November 20, 2009 6:21 PM
    Friday, November 20, 2009 11:16 AM