C# convert sql query into EF dbcontext linq to entities query

c# entity-framework-6 linq linq-to-entities sql

Question

I have an sql server database and I use EF 6.0 to access it in my app.

I have the following SQL Query I need to convert to dbcontext linq to entities query and have some damn hard time figure it out.

enter image description here

This is the query:

select 
PA.Number, 
PA.Name,  
PR.* 
from MYDBNAME.dbo.Product PR 
join MYDBNAME.dbo.Order OD on PR.Id = OD.Id 
join MYDBNAME.dbo.Payment PA on OD.Id = PA.Id 
where PR.Year = 2017
and PR.StatusId = (select CD.Id from Code CD where CodeId = (select ST.Id 
from Status ST where ST.Value = 'Done')
and CD.State = 'Completed') 
and PA.Created = '2018-12-10' 
and PR.Amount <= 500


class Product
{
public string Id { get; set; }
public string Name { get; set; }
public decimal Amount { get; set; }
public string StatusId { get; set; }
public int Year {get; set;}
} 


class Order
{
public string Id { get; set; }

} 

class Payment
{
public string Id { get; set; }
public DateTime Created { get; set; }
public decimal Amount { get; set; }
public string Number { get; set; }
public string Name { get; set; }
} 

class Status
{
  public string Id { get; set; }
public string Value { get; set; }
} 

class Code
{
public string Id { get; set; }
public string CodeId { get; set; }
public string State { get; set; }
} 

As State and Code classes are not related with the rest, I guess that subquery should be run separately and then issue another dbcontext query for the main query

1
0
2/28/2019 7:17:57 AM

Popular Answer

Your SQL equivalent LINQ query is,

string statusValue = "Done";
string codeState = "Completed";
DateTime paDate = DateTime.ParseExact("2018-12-10", "yyyy-MM-dd", new CultureInfo("en-US", true));
int year = 2017;
decimal amount = 500;

var result = (from PR in context.Products
              join OD in context.Orders on PR.Id equals OD.Id
              join PA in context.Payments on OD.Id equals PA.Id

              let codeId = (from ST in context.Status where ST.Value == statusValue select ST.Id).FirstOrDefault()
              let statusId = (from CD in context.Codes where CD.Id == codeId && CD.State == codeState select CD.Id).FirstOrDefault()

              where PR.Year == year
              && PR.StatusId == statusId
              && PA.Created == paDate
              && PR.Amount <= amount
              select new
              {
                  Number = PA.Number,
                  Name = PA.Name,
                  PR = PR
              }).GroupBy(x => x.Number).Select(x => x.First()).ToList();
0
2/28/2019 7:24:52 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow