Entity Framework 6 Recipes 2nd Edition (11-11), recipes11-11

Entity Framework 6 Recipes 2nd Edition (11-11), recipes11-11

11-11. Calling database functions in LINQ

Problem

It is required to call database functions in a LINQ query.

Solution

Suppose there is an Appointment entity model, as shown in Figure 11-11. We want to query all appointment in a given day of a week.

 

Figure 11-11.An Appointment entity with the start and end times for appointments

If we want to find the appointment for all Thursdays, we cannot use the runtime enumeration DayOfWeek. Thursday in the where clause to compare with the StartsAt attribute, because it cannot be translated into database statements.

We need to perform the following operations like Listing 11-18:

Listing 11-18.Using a Database Function in a LINQ Query

Class Program

{

Static void Main (string [] args)

{

RunExample ();

}

Static void RunExample ()

{

Using (var context = new EFRecipesEntities ())

{

Var app1 = new Appointment

{

StartsAt = DateTime. Parse ("7/23/2013 14:00 "),

GoesTo = DateTime. Parse ("7/23/2013 15:00 ")

};

Var app2 = new Appointment

{

StartsAt = DateTime. Parse ("7/24/2013 9:00 "),

GoesTo = DateTime. Parse ("7/24/2013 11: 00 ")

};

Var app3 = new Appointment

{

StartsAt = DateTime. Parse ("7/24/2013 13:00 "),

GoesTo = DateTime. Parse ("7/23/2013 15:00 ")

};

Context. Appointments. Add (app1 );

Context. Appointments. Add (app2 );

Context. Appointments. Add (app3 );

Context. SaveChanges ();

}

Using (var context = new EFRecipesEntities ())

{

Var apps = from a in context. Appointments

Where SqlFunctions. DatePart ("WEEKDAY", a. StartsAt) = 4

Select;

Console. WriteLine ("Appointments for Thursday ");

Console. WriteLine ("========================= ");

Foreach (var appointment in apps)

{

Console. WriteLine ("Appointment from {0} to {1 }",

Appointment. StartsAt. tow.timestring (),

Appointment. GoesTo. tow.timestring ());

}

}

}

}

The output result of the Listing 11-18 code is as follows:

Appointments for Thursday

======================================

Appointment from 9: 00 AM to 11: 00 AM

Appointment from 1: 00 PM to 3: 00 PM

How does it work?

Database functions can be used in eSQL and LINQ queries.

Methods In the SqlFunctions class are released. because these functions run on the Database End, their performance may be different from those of you. the net client may think differently, for example, on Thursday. NET DayOfWeek. the value of Thursday is 4. in the database, it is 5th days, so its value is 5.

Similar to the use of database functions in eSQL, not all database functions are supported by LINQ queries. You can view the list of all supported functions in Microsoft documents.

 

Appendix: script file of the database used in the Creation example