How to convert my SQL query to a LINQ statement

Question

I am not familiar with linq complex queries, how can I write the following?

Three tables: Library,Batch,Plan

Library: BatchId ProcessingQuantity

Batch: BatchId

Plan: PlanNo Name Quantity ExecState Date

SELECT b.ProductionPlanBatchId,
    a.ProductionPlanNo,
    a.ProductConfigName,
    sum(c.ProcessingQuantity) AS 'ProcessingQuantity',
    sum(a.Quantity) AS 'Quantity',
    a.ExecState,
    round(CONVERT(float,sum(c.ProcessingQuantity))/CONVERT(float,sum(a.Quantity)), 2) AS 'Percent',
    a.ProcessingCompletionDate
FROM ProductionPlan a,
    ProductionPlan_Batch b,
    ProductionLibrary c
WHERE a.ProductionPlanId = b.ProductionPlanId
AND b.ProductionPlanBatchId = c.ProductionPlanBatchId
AND a.ExecState <> 'Deleted'
GROUP BY b.ProductionPlanBatchId,
        a.ProductionPlanNo,
        a.ProductConfigName,
        a.ProcessingCompletionDate,
        a.ExecState
HAVING round(Convert(float,sum(c.ProcessingQuantity))/Convert(float,sum(a.Quantity)), 2) < 1
ORDER BY b.ProductionPlanBatchId DESC

It would be easier for us if you give us a proper class definition of your tables (only the relevant parts), together with the relations between the tables, together with the requirement that lead to your SQL statement. Please edit your question, and make the SQL statement easier to read, by adding a every now and then. Furthermore: what did you try? — Harald Coppoolse, Jul 01 '20 at 06:27
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, Jul 01 '20 at 23:43
A good start would be to find an OR mapper that supports LINQ. — Gert Arnold, Jul 02 '20 at 07:29

score 0 · Accepted Answer · answered Jul 02 '20 at 07:06

According to your description, I suggest you could try to use below linq.

Class:

[Table("ProductionLibrary")]
public partial class ProductionLibrary
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int ProductionPlanBatchId { get; set; }

    public int? ProcessingQuantity { get; set; }
}

[Table("ProductionPlan")]
public partial class ProductionPlan
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int ProductionPlanNo { get; set; }

    [StringLength(10)]
    public string ProductConfigName { get; set; }

    public int? Quantity { get; set; }

    [StringLength(10)]
    public string ExecState { get; set; }

    [StringLength(10)]
    public string ProcessingCompletionDate { get; set; }

    public int? ProductionPlanId { get; set; }
}

public partial class ProductionPlan_Batch
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int ProductionPlanBatchId { get; set; }

    public int? ProductionPlanId { get; set; }
}

Linq:

        var re = from a in dbcontext.ProductionPlans
                  from b in dbcontext.ProductionPlan_Batch
                  from c in dbcontext.ProductionLibraries
                  where a.ProductionPlanId == b.ProductionPlanId 
                  && b.ProductionPlanBatchId == c.ProductionPlanBatchId
                  && a.ExecState != "Deleted" 
                  select new
                  {
                      a.ExecState,
                      a.ProcessingCompletionDate,
                      a.ProductConfigName,
                      a.ProductionPlanId,
                      a.ProductionPlanNo,
                      a.Quantity,
                      b.ProductionPlanBatchId,
                      bProductionPlanId = b.ProductionPlanId,
                      c.ProcessingQuantity,
                      cProductionPlanId = c.ProductionPlanBatchId
                  }
                  into p
                  group p by new { 
                      p.ProductionPlanId, 
                      p.ProductionPlanNo , 
                      p.ProductConfigName,
                      p.ProcessingCompletionDate,
                      p.ExecState,
                      p.ProductionPlanBatchId
                  } into grpre
                  where Math.Round(((decimal)grpre.Sum(x => x.ProcessingQuantity))/((decimal)grpre.Sum(x => x.Quantity)), 2) <1
                  orderby grpre.Key.ProductionPlanBatchId descending
                  select new {
                      grpre.Key.ProductionPlanBatchId,
                      grpre.Key.ProductionPlanNo,
                      grpre.Key.ProductConfigName,
                      ProcessingQuantity = grpre.Sum(x =>x.ProcessingQuantity) ,
                      Quantity = grpre.Sum(x => x.Quantity),
                      grpre.Key.ExecState,
                      Percent = Math.Round(((decimal)grpre.Sum(x => x.ProcessingQuantity)) / ((decimal)grpre.Sum(x => x.Quantity)), 2),
                      grpre.Key.ProcessingCompletionDate
                  };

How to convert my SQL query to a LINQ statement

1 Answers1