Answered by:
Convert SQL Statement to LINQ...

Question
-
User-507786106 posted
How do I convert SQL Statement to C# LINQ?
SELECT TOP 10 * FROM TABLENAME WHERE CAST(CREATED, AS DATE) = CAST(GETDATE() AS DATE)
Please help me convert the above code to C# LINQ code ~ thank you
Wednesday, July 25, 2018 1:13 PM
Answers
-
User-369506445 posted
hi
you can try below code
var q = list.Where(x => DateTime.Compare(x.Date.Date, DateTime.Now.Date) == 0).Take(10).ToList();
<g class="gr_ gr_167 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="167" data-gr-id="167">also</g> I create a sample that shows how to use it, my sample is a list and you can use your database instead of my list
suppose you have a Table in your database similar below class
public class DbClass { public int Id { get; set; } public string Name { get; set; } public DateTime Date { get; set; } }
now your code
//fill hard code data List<DbClass> list = new List<DbClass>() { new DbClass(){Id =1 , Name = "Name 1" , Date=DateTime.Now}, new DbClass(){Id =2 , Name = "Name 2" , Date=DateTime.Now.AddDays(-1)}, new DbClass(){Id =3 , Name = "Name 3" , Date=DateTime.Now.AddDays(-2)}, }; var q = list.Where(x => DateTime.Compare(x.Date.Date, DateTime.Now.Date) == 0).Take(10).ToList(); foreach(var item in q) { Response.Write("Id : " + item.Id + " Name : " + item.Name + " Date :" + item.Date + "<br />"); }
result
Id : 1 Name : Name 1 Date :7/25/2018 5:59:07 PM
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, July 25, 2018 1:29 PM
All replies
-
User-369506445 posted
hi
you can try below code
var q = list.Where(x => DateTime.Compare(x.Date.Date, DateTime.Now.Date) == 0).Take(10).ToList();
<g class="gr_ gr_167 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="167" data-gr-id="167">also</g> I create a sample that shows how to use it, my sample is a list and you can use your database instead of my list
suppose you have a Table in your database similar below class
public class DbClass { public int Id { get; set; } public string Name { get; set; } public DateTime Date { get; set; } }
now your code
//fill hard code data List<DbClass> list = new List<DbClass>() { new DbClass(){Id =1 , Name = "Name 1" , Date=DateTime.Now}, new DbClass(){Id =2 , Name = "Name 2" , Date=DateTime.Now.AddDays(-1)}, new DbClass(){Id =3 , Name = "Name 3" , Date=DateTime.Now.AddDays(-2)}, }; var q = list.Where(x => DateTime.Compare(x.Date.Date, DateTime.Now.Date) == 0).Take(10).ToList(); foreach(var item in q) { Response.Write("Id : " + item.Id + " Name : " + item.Name + " Date :" + item.Date + "<br />"); }
result
Id : 1 Name : Name 1 Date :7/25/2018 5:59:07 PM
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, July 25, 2018 1:29 PM -
User-369506445 posted
you may get a <g class="gr_ gr_23 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="23" data-gr-id="23">linq</g> exception below like
The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.
if you get it <g class="gr_ gr_70 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="70" data-gr-id="70">trybelow</g> query
var q = db.Products2.ToList().Where(x => DateTime.Compare(x.LastBuy.Date, DateTime.Now.Date) == 0).Take(10);
Wednesday, July 25, 2018 2:19 PM -
User753101303 posted
Hi,
My personal preference would be (untested) :
var Today=DateTime.Today;
var Tomorrow=Today.AddDays(1);
var query=db.Data.Where(o=>o.Created>=Today && o.Created<Tomorrow).Take(10);I'm doing that even when using SQL as it allows to still use a possible index on the "Created" column while using CAST(CREATED AS DATE) prevents using an existing index.
IMHO at some point you should stop about trying converting SQL queries and start about thinking directly abnout writing Linq (ie C#) queries.
Wednesday, July 25, 2018 3:43 PM -
User1520731567 posted
Hi slimbunny,
According to your code,I think you want to filter data whose CREATED field is equal to the today.
There may some troubles If your CREATED field has HH:mm:ss... When you ignore HH:mm:ss to compare.
I suggest you could delete HH:mm:ss to compare.
You could use ToShortDateString() or ToString().Substring(0, 10).Trim().
For example:
var list = db.XXX.Where(_ => _.CREATED.ToShortDateString() == DateTime.Now.ToShortDateString()).Take(10).ToList();
or:
var list = db.XXX.Where(_ => _.CREATED.ToString().Substring(0, 10).Trim() == DateTime.Now.ToString().Substring(0, 10).Trim()).Take(10).ToList();
Other ways,you could refer to :
https://stackoverflow.com/questions/1601609/how-to-check-if-a-datetime-occurs-today
Best Regards.
Yuki tao
Thursday, July 26, 2018 5:29 AM