Convert datetime to a formatted string inside a LINQ-to-entities query
I ended up using the sql function FORMAT
; here's a simplified version of this implementation:
https://weblogs.asp.net/ricardoperes/registering-sql-server-built-in-functions-to-entity-framework-code-first
First you need to define the function in EF:
public class FormatFunctionConvention : IStoreModelConvention<EdmModel>
{
public void Apply(EdmModel item, DbModel model)
{
var payload = new EdmFunctionPayload
{
StoreFunctionName = "FORMAT",
Parameters = new[] {
FunctionParameter.Create("value", PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.DateTime), ParameterMode.In),
FunctionParameter.Create("format", PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.String), ParameterMode.In)
},
ReturnParameters = new[] {
FunctionParameter.Create("result", PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.String), ParameterMode.ReturnValue)
},
Schema = "dbo",
IsBuiltIn = true
};
item.AddItem(EdmFunction.Create("FORMAT", "CodeFirstDatabaseSchema", item.DataSpace, payload, null));
}
}
Then define it as C# methods:
public static class SqlFunctions
{
[DbFunction("CodeFirstDatabaseSchema", "FORMAT")]
public static String Format(this DateTime value, string format)
{
return value.ToString(format);
}
[DbFunction("CodeFirstDatabaseSchema", "FORMAT")]
public static String Format(this DateTime? value, string format)
{
return value?.ToString(format);
}
}
Register it in your DbContext
:
public class SqlDb : DbContext
{
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Conventions.Add(new FormatFunctionConvention());
}
}
And finally, you can call it like so:
var x = db.MyItems.Select(i => new { FormattedDate = SqlFunctions.Format(i.MyDate, "MM/dd/yyyy") }).ToArray();
Another option is using SqlFunctions.DateName, your code will be like this:
var offer = (from p in dc.CustomerOffer
join q in dc.OffersInBranch
on p.ID equals q.OfferID
where q.BranchID == singleLoc.LocationID
let value = (p.OriginalPrice - p.NewPrice) * 100 / p.OriginalPrice
orderby value descending
select new
{
Title = p.OfferTitle,
Description = p.Description,
BestOffer = value,
ID = p.ID,
LocationID = q.BranchID,
LocationName = q.CustomerBranch.BranchName,
OriginalPrice = SqlFunctions.StringConvert((decimal)p.OriginalPrice),
NewPrice = SqlFunctions.StringConvert((decimal)p.NewPrice),
StartDate = SqlFunctions.DateName("day", p.StartDate) + "/" + SqlFunctions.DateName("month", p.StartDate) + "/" + SqlFunctions.DateName("year", p.StartDate)
})
I found it useful if you don't want to add an extra select new block.
EDIT: Now that I understand the question, I'm giving it another shot :)
var offer = (from p in dc.CustomerOffer
join q in dc.OffersInBranch
on p.ID equals q.OfferID
where q.BranchID == singleLoc.LocationID
let value = (p.OriginalPrice - p.NewPrice) * 100 / p.OriginalPrice
orderby value descending
select new
{
Title = p.OfferTitle,
Description = p.Description,
BestOffer=value,
ID=p.ID,
LocationID=q.BranchID,
LocationName=q.CustomerBranch.BranchName,
OriginalPrice=SqlFunctions.StringConvert((decimal)p.OriginalPrice),
NewPrice=SqlFunctions.StringConvert((decimal)p.NewPrice),
StartDate=p.StartDate
})
.ToList()
.Select(x => new Offer()
{
Title = x.OfferTitle,
Description = x.Description,
BestOffer=value,
ID=x.ID,
LocationID=x.BranchID,
LocationName=x.CustomerBranch.BranchName,
OriginalPrice=x.OriginalPrice,
NewPrice=x.NewPrice,
StartDate=x.StartDate.ToString("dd.MM.yy")
}).First();
I know it's a bit long, but that's the problem with Linq To SQL.
When you use linq, the database call isn't executed until you use something such as ToList() or First() that results in actual objects. Once that SQL call is executed by the first .First() call, you're now working with .NET types, and can use DateTime stuff.