Linq To Sql - Get week day name from date

Question

Is it possible to construct a query in which I can retrieve week day name from a date into a separate column or variable?

I know I can very easily do this on .NET side but would like it to be in the query.

Answer 1

You can also use SqlFunctions...

var results=context.Listings
  .Select(l=>System.Data.Entity.SqlServer.SqlFunctions.DateName("dw",l.modify_date));

Of course, this only works when using a SQL Server. Methods that are cross-database, would be to use EntityFunctions.DateDiff with a known date to get the number of days between whatever and a known prior sunday, then modulus 7, then convert to a string.

Answer 2

It is possible you will need to build your query which returns day names and then join to your result on day number

int[] dayNum={1,2,3,4,5,6,7};

var result = from d in dayNum
    let dayOfWeek= (d == 1 ? "Monday" :
            d==2 ? "Tuesday" :
            d==3 ? "Wednesday" :
            d==4 ? "Thursday" :
            d==5 ? "Friday" :
            d==6 ? "Saturday" :
            d==7 ? "Sunday":"")
   let dn = d
 group d by new {dayOfWeek, dn} into dw
 select new { dw.Key.dayOfWeek, dw.Key.dn};

The result of this will be