locked
Convert SQL Statement to LINQ... RRS feed

  • 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