Understanding the Basics of LINQ Entity Framework

linq and entity frameworkLINQ and Entity Framework are essentially two different technologies but there are myriad of interdependencies between the two technologies which make them intertwine. LINQ stands for Language Integrated query and is Microsoft’s alternative to SQL. The benefits and simplicity of LINQ make it first choice database query language for the .NET developers. On the contrary, Entity Framework, often pronounced as EF, is an object relational model approach towards database management. In Entity Framework, every table in a database is modeled to a corresponding object in the object relational model and all the records in the table are stored in the collection of objects where each object represents a class. The tutorial explains database first approach where the entity data model is generated through existing database. Then, LINQ queries are applied on the entity data model to query the database.

To learn more about LINQ, take a course at Udemy.com.

Creating a local database

To add a simple local database to a console application, follow these steps:

1-     Create a new console application in visual studio 2012 or 2013 and give it any name.

2-     Create a local database named HospitalDB by right clicking on the Project name of the console application and then selecting Add = > New Item and then selecting the Local Database template.

3-     Name the database, HospitalDB.

4-     To add a table to the HospitalDB database. Click on explore HospitalDB icon just before the database name. Right click on the tables option and choose Create Table option as shown in the following figure:

 

linq&EF-1

 

5-     Name the new table Patient. The definition of the Patient table should look like this:

linq&EF-2

6-     In the same manner, add a new table named Test. The definition of the Test table should look like this:

linq&EF-3

7-     A patient can have many tests in a hospital i.e a blood test, a urine test, an x-ray test, AIDS test and so on. But one copy of the test belongs to one patient. This is one to many relationship between patient and table. To add this relationship in the HospitalDB database, right click on the Test table, choose Table Properties and then Add Relations in the window that appears. The relationship window should look like this:

linq&EF-4

Click on the Add Relation button at the bottom of the above window. And click OK.

8-     By right clicking on table names, select Show Table Data and add following data to the tables. The tables should look like this:

Patient Table

linq&EF-5

Test Table:

linq&EF-6

For more Entity Framework concepts, take a course at Udemy.com

Entity Framework Database First Approach

Follow these steps to entity data model using database first approach.

1-     Right click on the project name, click Add -> New Item.

 

2-     From the templates that appear, select ADO.NET Entity Data Model which is located under the Data items.

The following figure shows how to add ADO.NET Entity Data Model.

linq&EF-7

 

3-     Name the model HospitalModel and click Add button.

 

4-     There are two options in the wizard that appears: Generate from database or an Empty model. Choose Generate from database and click on the finish button. In the Connection window that appears, chose connection to HospitalDB database that has just been created and click next.

 

5-     In the new window, check the Table check box. This will add both Patient and Test table to the EF data model. This is shown in the following figure:

linq&EF-8

Accessing EF Objects via LINQ

The best way to interact with entity framework object is through LINQ. The process is straight forward and has been explained in the following scenario:
Suppose, if the FirstName, LastName and the Age field for all the records in the Patient table of the HospitalDB database, have to be accessed. The following LINQ code can be integrated into the Main method of the console application.

using (HospitalDBEntities hospitalcontext = newHospitalDBEntities())

{

IQueryable<Patient> patients = hospitalcontext.Patients;

 

foreach(Patient p in patients)

{

Console.WriteLine(p.FirstName +" "+p.LastName + " "+p.Age);

 

}

}

 

In the above example, the HospitalDBEntities is a class that is automatically generated by the HospitalModel. This class maps to the HospitalDB database. The HospitalDBEntities class implements IDisposable interface therefore it is convenient to instantiate and use it within the using keyword. Next, to access the classes, such as Patient and Test which refers to the Patient and Test tables in the HospitalDB database the dot operator is used with the entity model name i.e. HospitalDBEntities in the above example. This returns a collection of Patient objects which implements the IQueryable<T> interface which can then be enumerated to access the fields such as FirstName, LastName and Age within each object in the patients’ collection.

LINQ Entity Framework for implementing Database Associations

The Patient and Test table in the Hospital DB database are associated with each other via one to many relationship. A patient can have multiple tests. To access all the tests associated with a particular patient, LINQ can be used. This is explained in the following example:

IQueryable<Patient> patients = hospitalcontext.Patients;

 

foreach(Patient p in patients)

{

 

Console.WriteLine(p.FirstName + "has following treatment record:");

foreach(Test t in p.Tests)

{

Console.WriteLine(t.Name +" : "+t.Remarks);

}

Console.WriteLine("********************************");

 

}

In the above example, the FirstName of the patient is accessed in the outer foreach loop. Each Patient object in the patient collection contains a collection of Test objects that have similar values in their PatientId field and the Id field of the Patient objects which contains these collections. This mapping between the Patient and the Test classes in the HospitalDBEntities actually correspond to the relationship between the Patient and Test tables in the HopsitalDB database where PatientId was the foreign key in the Test table that corresponded to the Id primary key of the Patient table.

Similarly, to access particulars of all the Patients who underwent Blood examination, the following LINQ query can be used:

IQueryable<Test> tests = hospitalcontext.Tests.Where(t => t.Name == "Blood");

 

foreach (Test t in tests)

{

Console.WriteLine(t.Patient.FirstName +" "+ t.Patient.LastName + " "+ t.Patient.Age);

}

Want to learn more about LINQ Entity Framework? Take a course at Udemy.com.