What is the equivalent of CASE WHEN THEN (T-SQL) with Entity Framework?
In this case, I'd say the conditional operator (p ? x : y
) is a good substitute.
// context.MyTable is an IQueryable<MyTable>
var query = from t in context.MyTable
group t by t.Code into grp
select
new {
Code = grp.Key,
Jan = grp.Sum(x => x.Month == 1 ? x.Days : 0),
};
Or combine a Where
and a Sum
:
Jan = grp.Where(x => x.Month == 1).Sum(x => x.Days),
I'm not sure what SQL these translate to exactly, but they should both have the same result.
Alternative to parameter for case statement
So instead of using the case statement with the parameter, use it as your filter. So your view would be something like (best practice is to always include schema on your objects, I assumed dbo):
view
select warehouse
, productStatusId
, productId
from dbo.products p
left join dbo.productstatus s on p.availability = s.cd;
query to use view
select productId
, productStatusId
from schema.viewname
where warehouse = @warehouse;
another way to get all warehouse statuses
select cast(case when warehouse = @warehouse then 1 else 0 end as bit) as AtThisWarehouse,
productStatusId
from schema.viewname;
SQL to LINQ - Case Statement
Try using ternary operator for all cases in your ThenBy
clause:
.ThenBy(x => x.column3 == "X"
? 1
: x.column3 == "Y"
? 2
: x.column3 == "Z"
? 3
: defaultOrder)
How can I achieve SQL CASE statement from LINQ
You can use this if it suits you.
I would just explain the LINQ query part.
You can use this with EF. I created dummy data for these.
For EF, use IQueryable instead.
// from a row in first table
// join a row in second table
// on a.Criteria equal to b.Criteria
// where additional conditions
// select the records into these two fields called All and Range
// Convert the result set to list.
var query = (from a in lstCalc
join b in lstSampleSet
on a.SampleSetID equals b.ID where b.SampleDrawn >= DateTime.Now.AddMonths(-8)
&& b.Department == "Location A"
select new { All = 0, Range = Utilities.RangeProvider(a.Value) }).ToList();
EDIT : LINQ Query for grouped result.. Make sure you are using IQueryable.
var query = (from a in lstCalc
join b in lstSampleSet
on a.SampleSetID equals b.ID where b.SampleDrawn >= DateTime.Now.AddMonths(-8)
&& b.Department == "Location A"
group a by Utilities.RangeProvider(a.Value) into groupedData
select new Result { All = groupedData.Sum(y => y.Value), Range =
groupedData.Key }).ToList();
Here is the code for the same.
public class Program
{
public static void Main(string[] args) {
List<Calculation> lstCalc = new List<Calculation>();
lstCalc.Add(new Calculation() {SampleSetID=1, Value=10 });
lstCalc.Add(new Calculation() { SampleSetID = 1, Value = 10 });
lstCalc.Add(new Calculation() { SampleSetID = 2, Value = 20 });
lstCalc.Add(new Calculation() { SampleSetID = 3, Value = 30 });
lstCalc.Add(new Calculation() { SampleSetID = 4, Value = 40 });
lstCalc.Add(new Calculation() { SampleSetID = 5, Value = 50 });
lstCalc.Add(new Calculation() { SampleSetID = 6, Value = 60 });
lstCalc.Add(new Calculation() { SampleSetID = 7, Value = 70 });
lstCalc.Add(new Calculation() { SampleSetID = 8, Value = 80 });
lstCalc.Add(new Calculation() { SampleSetID = 9, Value = 90 });
List<SampleSet> lstSampleSet = new List<SampleSet>();
lstSampleSet.Add(new SampleSet() {Department = "Location A", ID=1, SampleDrawn=DateTime.Now.AddMonths(-5)});
lstSampleSet.Add(new SampleSet() { Department = "Location A", ID = 2, SampleDrawn = DateTime.Now.AddMonths(-4) });
lstSampleSet.Add(new SampleSet() { Department = "Location A", ID = 3, SampleDrawn = DateTime.Now.AddMonths(-3) });
lstSampleSet.Add(new SampleSet() { Department = "Location A", ID = 4, SampleDrawn = DateTime.Now.AddMonths(-2) });
lstSampleSet.Add(new SampleSet() { Department = "Location A", ID = 5, SampleDrawn = DateTime.Now.AddMonths(-2) });
lstSampleSet.Add(new SampleSet() { Department = "Location A", ID = 6, SampleDrawn = DateTime.Now.AddMonths(-2) });
lstSampleSet.Add(new SampleSet() { Department = "Location A", ID = 7, SampleDrawn = DateTime.Now.AddMonths(-1) });
var query = (from a in lstCalc
join b in lstSampleSet
on a.SampleSetID equals b.ID where b.SampleDrawn >= DateTime.Now.AddMonths(-8)
&& b.Department == "Location A"
select new { All = 0, Range = Utilities.RangeProvider(a.Value) }).ToList();
Console.WriteLine(query.Count);
Console.ReadLine();
}
}
public class Utilities
{
public static string RangeProvider(int value)
{
if (value > 0 && value <= 25)
{ return "Low"; }
if (value > 25 && value <= 75)
{ return "Medium"; }
if (value > 75 && value <= 90)
{ return "High"; }
else
{ return "Very High"; }
}
}
public class Result {
public int All { get; set; }
public string Range { get; set; }
}
public class Calculation
{
public int SampleSetID { get; set; }
public int Value { get; set; }
}
public class SampleSet
{
public int ID { get; set; }
public DateTime SampleDrawn { get; set; }
public string Department { get; set; }
}
Entity Framework core how to use Case When statement
You can do like this.
DateTime dt1 = DateTime.Now;
DateTime dt2 = DateTime.Now;
DateTime dt3 = DateTime.Now;
DateTime dt4 = DateTime.Now;
var query = _context.Leave_Tbl.Sum(p => EF.Functions.DateDiffDay(dt1 > dt2 ? dt3 : dt4, dt1 > dt2 ? dt3 : dt4));
I have just taken these dates for simplicity.
Can you use a CASE statement with OrderBy in an LINQ to Entities query?
var p = ctx.People.OrderBy(p => (p.IsQualityNetwork == 1 || p.IsEmployee == 1) ? 0 : 1)
.ThenBy(p => p.Name);
Related Topics
How to Stop an Application from Opening
Convert from Word Document to HTML
Setup Database (Sqlite) for Unity
Linux to Windows Bad Encoding Response
How to Write My C++ Function So I Can Call It from C#
How to Get The Http Post Data in C#
Assemblytitle' Attribute in the .Net Framework
Generate PDF from ASP.NET from Raw HTML/CSS Content
How to Add a Custom View Dynamically to a View in Visual Studio for MAC C#
Non-Virtual Interface Design Pattern in C#/C++
How to Add a Local Script File to The HTML of a Webbrowser Control
Androidapp and MySQL connection Didn't Work Connection.Open
Mono: Is Remote Debugging Possible with Monodevelop
How to Add a Reference to an Unmanaged C++ Project Called by a C# Project
How to Evaluate Expression Whilst Debugging