How to get month name from datetime column in Linq to SQL?

Question

I have a DateTime column in my table.

In the Linq to SQL query, I only need the month part of this field

How to write a Linq query for this?

Answer 1

Well, there are three options that I can immediately think of:

• Pull all DateTime back and then retrieve the month and format it on the client side
• Use DateTime.Month to pull only a month ago from the database and then format it on the client side
• Try to get formatting (conversion to text) to work with the database

Personally, I would not try the third option - it seems to me that this is very unlikely to work, at least without much effort.

I suspect that I’m really just going back to DateTime and doing everything on the client side. It is very easy to do, and it is work related. I expect DateTime.Month to probably work, but you will not save very much network bandwidth and you will get a server to do the work, which is probably just as easy for the client.

Note that if you still want the results to be queried, you can use AsEnumerable to force the rest of the request to be executed on the client side. For instance:

 var query = db.Customers .Where(c => c.OrderCount > 500) // Or whatever query you want .Select(c => new { c.Name, c.FirstOrder }) .AsEnumerable() // Do the rest of the query on the client .Select(c => new { c.Name, c.Month = c.FirstOrder.ToString("MMM") }); 

EDIT: As noted in the comments, it makes sense to use DateTime.Month on the server side if it really affects the results, for example.

 var query = db.Customers.Where(c => c.FirstOrder.Month == 1) ... 

... but in those cases, I expect it to be a numerical value, which is important, not the name of the month.

Answer 2

Sort of:

 string monthName = dataContext.GetTable<TableName>() .FirstOrDefault(t => t.DateTimeColumn) .ToString("MMM"); 

Answer 3

You need to convert the table to Enumerable type with ToList() , because you cannot use ToString() for the query type.

 var result = from sales in SalesOrderHeaders.ToList() select new { Sales_Order_ID = sales.SalesOrderID, Territory_ID = sales.TerritoryID, Month = sales.OrderDate.ToString("MMMM"), Year = sales.OrderDate.Year }; result.Dump();