'TimeOfDay' is not supported in LINQ to Entities - Find total seconds in Linq 2 SQL

Question

When I do the following :

[EdmFunction("Edm", "TruncateTime")]
public static DateTime? TruncateDateTime(DateTime? inputDate)
{
    return inputDate.HasValue ? inputDate.Value.Date : (DateTime?)null;
}

 [EdmFunction("Edm", "TotalSeconds")]
public static double? GetTotalSeconds(DateTime? inputDate)
{
    return inputDate.HasValue ? inputDate.Value.TimeOfDay.TotalSeconds: (double?)null;
}

var employeeSelection = (from c in MyContext.Employees.Where(c => c.From.HasValue && c.To.TimeOfDay.TotalSeconds != 0)
...
select new Employee

{


 To = c.To != DateTime.MinValue && c.To.TimeOfDay.TotalSeconds != 0 ? TruncateDateTime(c.To).Value :
              c.To != DateTime.MinValue && c.To.TimeOfDay.TotalSeconds == 0 ? c.From.Value.AddMinutes(30) :  DateTime.MinValue

}

I get this :

Additional information: The specified type member 'TimeOfDay' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

And when I try this :

var employeeSelection = (from c in MyContext.Employees.Where(c => c.From.HasValue && GetTotalSeconds(c.To) != 0)
...
select new Employee

{


 To = c.To != DateTime.MinValue && GetTotalSeconds(c.To) != 0 ? TruncateDateTime(c.To).Value :
              c.To != DateTime.MinValue && GetTotalSeconds(c.To) == 0 ? c.From.Value.AddMinutes(30) :  DateTime.MinValue

}

I get :

Additional information: The specified method 'System.Nullable1[System.Double] GetTotalSeconds(System.Nullable1[System.DateTime])' on the type 'GetDateRangeMethod' cannot be translated into a LINQ to Entities store expression.

How can I find the total seconds in Linq to SQL ?

Thanks

Require

try to use DbFunctions msdn.microsoft.com/en-us/library/… – Favourable

Answer 1

Try using SqlFunctions.DatePart.

e.g. instead of

var employeeSelection = (from c in MyContext.Employees
          .Where(c => c.From.HasValue && GetTotalSeconds(c.To) != 0)

try something like

var employeeSelection = (from c in MyContext.Employees
          .Where(c => c.From.HasValue && SqlFunctions.DatePart("second", c.To) != 0)

For NetMage

var employeeSelection = (from c in MyContext.Employees
          .Where(c => c.From.HasValue && (SqlFunctions.DatePart("second", c.To) + (60 * SqlFunctions.DatePart("minute", c.To)) + (3600 * SqlFunctions.DatePart("hour", c.To))) != 0)

Where

TotalSeconds = (SqlFunctions.DatePart("second", c.To) + (60 * SqlFunctions.DatePart("minute", c.To)) + (3600 * SqlFunctions.DatePart("hour", c.To)))

Rectrix

The two functions aren't the same thing. – Buber

@Buber Indeed they aren't, this is what a 'helpfull' answer because I show a way to get the seconds, and then expand upon this answer to give: TotalSeconds = SqlFunctions.DatePart("second", c.To) + (60 * SqlFunctions.DatePart("minute", c.To)) + (3600 * SqlFunctions.DatePart("hour", c.To)) – Rectrix

Answer 2

Have a look at Date and Time Canonical Functions for LINQ-to-Entities and if you are unsure on how to call them, have a look at How to: Call Canonical Functions

The methods that are available should be able to solve the problem your currently having where the operations your currently using are not supported by the database.

Quarrel

Answer 3

If you have a Linq-To-Entities Query you can only perform operations which can be executed by your used database management system.

You will need to get your desired information from your DB and create a list from that query, than that will be called Linq-To-Objects and you will be able to perform you desired operation.

Milker

When speaking on hundreds and thousands of records this solution will take a LONG time . – Require

it depends on how many columns you are selecting. You need to filter your desired data and not select everything where 80% maybe is not needed. – Milker