CODEWITHSUNDEEP
c#entity-frameworklinqdatetimetimespan
JAN
JAN

Reputation: 21865

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

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

Upvotes: 2

Views: 4254

Answers (3)

BLoB
BLoB

Reputation: 9725

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)))

Upvotes: 4

Stuart
Stuart

Reputation: 764

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.

Upvotes: 2

Dom84
Dom84

Reputation: 864

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.

Upvotes: -1

Related Questions