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.
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
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();