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

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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