I am joining three tables and I would like to get date without time.
var query = from anObjective in db.Objective
join anObjectiveType in db.ObjectiveType
on anObjective.IdType equals anObjectiveType.IdObjectiveType
join statusCode in db.StatusCode
on anObjective.IdStatusCode equals statusCode.IdStatus
select new
{
IdObjective = anObjective.IdObjective,
ObjectiveName = anObjective.ObjectiveName,
DateCreation = anObjective.DateCreation, //get just Date without time
DateEnd = anObjective.DateEnd, //get just Date without time
};
DbFunctions.TruncateTime
public partial class Objective
{
public int IdObjective { get; set; }
public string ObjectiveName { get; set; }
public Nullable<System.DateTime> DateCreation { get; set; }
public Nullable<System.DateTime> DateEnd { get; set; }
}
anObjective.DateCreation
anObjective.DateEnd
DateCreation =anObjective.DateCreation.Date,
//or
DateCreation =null ? default(DateTime?) : anObjective.DateCreation.Date,
'System.Nullable' does not contain a definition for
'Date' and no extension method 'Date' accepting a first argument of
type 'System.Nullable' could be found (are you
missing a using directive or an assembly reference?)
DateCreation =anObjective.DateCreation.Value.ToShortDateString(),
anObjective.DateEnd.Value.ToShortDateString(),
LINQ to Entities does not recognize the method 'System.String
ToShortDateString()' method, and this method cannot be translated into
a store expression
DateCreation =anObjective.DateCreation.Value.Date,
The specified type member 'Date' is not supported in LINQ to Entities.
Only initializers, entity members, and entity navigation properties
are supported.
First, recognize that there is no built-in type for a date without a time in .NET. At least not yet anyway. So if you want date-only, then you'll need it in a string in some particular format.
You're not going to be able to get the underlying provider of a LINQ query to understand the conversion of DateTime
to string
. So, you'll need to query for the data in its original form, and then convert it to string after the query results are materialized.
Start with your original query, unmodified from what you showed at the top of your question. Then add the following:
var results = query.AsEnumerable().Select(x=>
new
{
IdObjective = x.IdObjective,
ObjectiveName = x.ObjectiveName,
DateCreation = x.DateCreation.ToShortDateString(),
DateEnd = x.DateEnd.ToShortDateString()
});
You have some options also:
If you know you want a List
or an Array
, then you can use ToList()
or ToArray()
instead of AsEnumerable()
.
If you want the resulting string to be in a specific format, or use a specific culture, then you can use ToString
instead of ToShortDateString
For example, you might want an array containing a standard ISO-8601 date string and you might want to use the Invariant culture to avoid side effect when the current culture uses a non-Gregorian calendar system.
var results = query.ToArray().Select(x=>
new
{
IdObjective = x.IdObjective,
ObjectiveName = x.ObjectiveName,
DateCreation = x.DateCreation.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture),
DateEnd = x.DateEnd.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)
});