Convert the SQL Query in to Linq Query

Question

I am new to EF Core Framework and trying to learn it. I have tried executing a SQL Query and got the response from the DB but now trying to convert the below SQL query in to Linq query to use it with the EF Core. My SQL Query is like below

public static string shipQuery = @"SELECT distinct newid() as  Id
                  ,[prj_number]
                  ,[ship_Date]
                  ,[delivery]
                  ,,CONVERT(VARCHAR(10),  [delDate], 110) as delDate
                  ,[po]
                  ,[so]      
                  ,SUM([qty_so]) as total_qty
                  FROM [sDev].[dbo].[PrjDetails] 
                  where [Rep_Prj] = @rep and [so_status] not in ('Canceled','Voided lines') 
                  group by Id,[prj_number],[ship_Date],[delivery],delDate,[po],[so]
                  ORDER BY ship_Date DESC";

I have used this SQL Query in to my EF Core Framework to get the data like

_context.shipByRepo.FromSql<shipByRepo>(shipQueries.shipQuery, rep).ToList();

This works as expected. To improvise the framework I am trying to convert the query in to Linq query. As intial try I just filtered by the rep

   _context.shipByRepo.Where(w => w.Reporting_Project == rep).ToList();

And this works, but I am not sure how can I do the above like like doing the date conversion& sum along with the Group by and Orderby

Earlier with the SQL Query I had the model as

  public class shipByRepo : IshipByRepo
  {
    public Guid Id { get; set; }
    public string prj_number { get; set; }
    public string ship_Date { get; set; }
    public string delivery { get; set; }
    public string delDate { get; set; }
    public string po { get; set; }
    public string so { get; set; }
    public decimal? total_qty { get; set; }
  }

But with the Linq query I changed I modified the Model class without including the total quantity/Converted date fiels

  public class shipByRepo : IshipByRepo
  {
    public string prj_number { get; set; }
    public string ship_Date { get; set; }
    public string delivery { get; set; }
    public DateTime? delDate { get; set; }
    public string po { get; set; }
    public string so { get; set; }
  }

What is the value of the gui here? I don't understand how it can have any value since it is not stored in the data base is is just generated as the values are selected. — Hogan, Sep 13 '19 at 19:02
also, since you are generating a unique GUID on every row, DISTINCT does nothing. — Hogan, Sep 13 '19 at 19:03
@Hogan I need a unique Id on each record for the Entity frmaework to work — trx, Sep 13 '19 at 19:07
@trx -- this is the wrong way to solve that problem. You should add a unique id to the table — Hogan, Sep 13 '19 at 19:15
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might get you started? — NetMage, Sep 13 '19 at 19:19
@Hogan I have unique ID on the table but It doesnt help me with the grouping by — trx, Sep 13 '19 at 19:26
A unique ID you create as a query result will not help EF to associate rows back to a table. What do you expect EF to do with the unique ID? I don't think your SQL Query makes sense either, having both `DISTINCT` and `GROUP BY` `Id`, which is presumably unique per answer row? — NetMage, Sep 13 '19 at 20:11

score 0 · Answer 1 · answered Sep 13 '19 at 20:30

I think below code may help you

    _context.shipByRepo
    .Where(x => x.Rep_Prj == rep && !x.so_status.Contains("Canceled") 
    && !x.so_status.Contains("Voided lines"))
    .OrderByDescending(x => x.ship_Date).Distinct().ToList();

if you want to select number of fields that use .Select(x=> new { x.propertyName, x.anotherOne })

score 0 · Answer 2 · answered Sep 16 '19 at 07:43

0

You could use OrderBy(OrderByDescending) and GroupBy like below:

_context.shipByRepo.OrderByDescending(x=>x.ship_Date)
                   .GroupBy(x=>new { x.prj_number,x.ship_Date,x.delivery,x.so,x.po,x.delDate})
                   .ToList();

answered Sep 16 '19 at 07:43

Rena

30,832
6
37
72

Convert the SQL Query in to Linq Query

2 Answers2