How to get first day of month/year etc?

Question

I'm using Linq.Dynamic to query a SQL Server database.

I have a date field named 'EnteredOn' which I need to convert to first second of that month (or year, minute etc)

I tried basically everything, but everything throws an error

Here are some of my trials

Return $"DateTime({FieldName}.Year,1,1)"
Return $"DateTime({FieldName}.Year,{FieldName}.Month,1)"
Return $"DateTime({FieldName}.Year,{FieldName}.Month,{FieldName}.Day)"
Return $"DateAndTime.DateDiff(2, 0, {FieldName})"
Return $"EntityFunctions.CreateDateTime({FieldName}.Year,{FieldName}.Month,{FieldName}.Day,0,0,0)"
Return $"DbFunctions.CreateDateTime({FieldName}.Year,{FieldName}.Month,{FieldName}.Day,0,0,0)"

Either I get an error that I can only use parameterless constructors (so first 3 dont work), or that no such property or field exists (DateDiff), or it doesnt recognize the Class

How can this be done?

Some of the error messages

No applicable method 'DateDiff' exists in type 'viwAttendancePre'
No property or field 'Microsoft' exists in type 'viwAttendancePre'
No property or field 'Month' exists in type 'viwAttendancePre'
LINQ to Entities does not recognize the method 'System.Nullable1[System.DateTime] CreateDateTime(System.Nullable1[System.Int32], System.Nullable1[System.Int32], System.Nullable1[System.Int32], System.Nullable1[System.Int32], System.Nullable1[System.Int32], System.Nullable`1[System.Double])' method, and this method cannot be translated into a store expression.
No applicable method 'CreateDateTime' exists in type 'EntityFunctions'
No property or field 'DbFunctions' exists in type 'viwAttendancePre'
Only parameterless constructors and initializers are supported in LINQ to Entities.
No applicable method 'Date' exists in type 'viwAttendancePre'

Environment

Linq Package

 <Reference Include="System.Linq.Dynamic, Version=1.0.6132.35681, Culture=neutral, processorArchitecture=MSIL">
<HintPath>..\..\_packages\System.Linq.Dynamic.1.0.7\lib\net40\System.Linq.Dynamic.dll</HintPath>
<Private>True</Private>
</Reference>

UPDATE -Using System.Linq.Dynamic.Core

I see know that if i don't use the fields in the query it does work as follows

Dim d = GetSystemContext()
Dim goodq = d.Set(GetType(Attendance)).AsQueryable.Select("new (DateTime(2000,1,1) as mydate)")' this returns just fine
Dim badq = d.Set(GetType(Attendance)).AsQueryable.Select("new (DateTime(AddedOn.Year,1,1) as mydate)")' this throws "System.NotSupportedException: 'Only parameterless constructors and initializers are supported in LINQ to Entities.'"

Answer 1

It should be possible using System.Linq.Dynamic.Core.

EFCore

void Main()
{
    var r = Reservations.Select("DateTime(CheckinDate.Year,1,1)");
    r.Dump();
}

EFCore Result

EFCore Generated SQL

EF 6

For Entity Framework 6, you need to use:

var datesDynamic = ctx.Reservations.Select("DbFunctions.CreateDateTime(CheckinDate.Year, 1, 1, 0, 0, 0)");

Note that you need to provide all parameters to the CreateDateTime method.