LINQ to SQL & Entity Framework Understanding Similarities and Differences

Entity framework is Microsoft’s latest data access technology and is considered a replacement for ADO.NET. However, there are still plenty of features where ADO.NET beats entity framework, but developers are now switching to Entity Framework owing to its strong decoupling and separation of concerns between the business and data access logic. This article is not focused towards explaining ins and outs of entity framework; this article explains that how LINQ can integrate with entity framework to achieve the desired data access feature. We will first start with our article by explaining how LINQ to SQL and then we will move towards LINQ to Entity framework. If you are not much familiar with foundations of LINQ, I would advise you to first have a look at my other tutorials on Getting Started with LINQ and Interpreted LINQ queries.

Basically both LINQ to Entity Framework and LINQ to SQL are object-relational mappers on which LINQ queries can be executed. The major difference between LINQ to SQL & LINQ to EF is in the coupling between the database scheme and the classes that are used to query that database via LINQ. The coupling in case of LINQ to SQL is very strong between the database schema and query classes, whereas in case of LINQ to EF, the coupling between underlying database and the querying classes is loose because you query database using Entity Data Model which introduces another layer abstraction between the database and querying classes. LINQ to SQL or commonly called L2S was developed by the C# team of developers at the Microsoft, while EF was developed by the team that developed EF. L2S was released with .NET Framework 3.5 and EF was released in the first service pack to that framework. Currently, ADO.Net team has also taken over L2S and all of its features.

LINQ to SQL

You can use any ordinary class for querying database using LINQ to SQL if you include proper markup to that class. Let us suppose that we have a School database where we have a table named Student. This table has three attributes: ID, Name & Age. We want to query that database table via LINQ to SQL class; we can do this by creating an ordinary C# class, and decorating it properly with some attributes that are required to convert an ordinary C# class to a LINQ to SQL Entity Class. Let us first design an ordinary class that maps to the table in the database.

We can create an ordinary class; we can name this class Student, similar to the name of the corresponding table. Inside our student class, we can have an integer type variable ID, a string type variable ‘Name’ and an integer type variable which Age. Closely pay attention that here in our Student class, we have an ID variable which corresponds to ID attribute, a Name variable which corresponds to Name attribute & and an Age variable that corresponds to the Age attribute of the Student table in School database.

Note: To see how we can create a database and table via Visual Studio 2010, refer to this tutorial.

Coming back to our tutorial, let us first create a simple class in C# that can correspond to the Student table of the school database. Have a look at following class.

Code:

public class Student
{
    public int ID;
    public string Name;
    public int Age;
}

This is a simple C# class and it has a name that matches the name of the Student table and it has member variables that match with the attributed of the Student table. However, we can still not use it as L2S entity class. To convert this class into L2S entity class, we have to decorate it with some special syntax which we are going to do next. Make following changes in the above Student class.

Code:

[Table]
public class Student
{
    [Column (IsPrimaryKey=true)]
    public int ID;

    [Column]
    public string Name;

    [Column]
    public int Age;
}

In the above code, you can see that we have added a [Table] element before the class name which signifies that this class is used to interact with a table in database. Basically, [Table] is an attribute which is located in System.Data.Linq.Mapping namespace. [Table] class signifies that the object of this type actually corresponds to a row in the database. The class having [Table] attribute with it, referring to a database table is called an entity in LINQ to SQL. By default, it is assumed that the name of the class should match exactly with the table to which it is referring. For instance, since we have a table Student, the class we are developing to refer to it has a similar name. You can give any name to the class but in that case you will have to slightly modify the [Table] attribute to include information about the table to which the class would refer. The modification for this is as follows:

Code:

[Table (Name="Student")]

Here, you have supplied the information about the table in the [Table] attribute, now you can give any name to the following entity class.

Now, come inside the Student class; Like [Table] attribute that corresponds to a table in database, we have [Column] attributed that refers to a column in the corresponding table. Have a look at the first [Column] attribute in our class which is referring to ID in the database. In this [Column] attribute we have specified that this variable refers to the primary key in the table. This is particularly useful because we can uniquely identify object based on this member variable. Also, when we are updating a record in the database, we can use this primary key column attribute to update the correct row in the database via entity class.

Like, the [Table] attribute, you can also specify the name of the column to which you want to refer in the [Column] attribute. It will eliminate the need for an exact match between your member variable and the corresponding column attributes in the table. For instance:

Code:

[Column (Name= "Age")]

Using the above [Column] attribute, you can give any name to the member variable which refers to the Age attribute in the Student table.

Apart from have public fields, you can use public properties with private fields. As with all the properties, this allows you to embed validation logic while accessing your code. However, you specify the name of the corresponding column in the “Column” attribute, the property would be bypassed and L2S would write directly to the private filed while populating data from the database.

You don’t have to worry about writing these complex LINQ to SQL entity classes yourself because, Visual Studio lets you create one if you right click on the project name, click on add new item and find LINQ to SQL classes in the list of items that appear. You however, need to click on “Data” option on left in the type of items. A complete code sample on how we can use LINQ to SQL to query tables in the database has been presented in this article. Look at the last two examples of that. For now on, our main focus will be LINQ to Entity Framework, which are Microsoft’s most advanced and probably the most fascinating data access technology. However, you can query entity frame work classes using LINQ 2 SQL as well with slight modification such as by using DataContext object instead of ObjectContext etc.

LINQ to Entity Framework

We mentioned earlier that in case of entity framework, the coupling between a database schema and the corresponding query classes is loose due to the introduction of a new layer between them i.e. Entity Data Model, or more commonly referred as the entity framework. An entity data model basically has three parts.

• A high level Conceptual Model, which is basically a way to abstractly model the underlying database without having a connection to the database.
• The second part of EDM is the store model. The store model actually describes the scheme of the database.
• The third part of an EDM is the mapper that is basically used to map Conceptual model to the store model.

Now, before continuing to further discussion, I would want you to add a local database “School’ to your project. Add one table to this database and name this table, “Student”. Add some values to this table. The table should look like as the one in the following figure.



Now, I will tell you that how you can add an ADO.NET Entity Frame Work in your project. Follow these steps:

1. Right click on the project, Click, Add => New Item.
2. From the list of items, chooseADO.NET Entity Data Model and name it SchoolEDM.edmx. If you cannot find ADO.NET Entity Data Model option in the list of items, Click, on Data, under Installed Templates on the left section of the visual studio. Have a look at this figure for better understanding



3. Click on the Add button and you will see a screen appear, that will ask you to choose Model Contents. Click on Generate from database option and click next button. See following figure



4. The screen that appears would ask you to choose your data connection. Select, School.sdf database. If you cannot find it right away, click on the drop down menu and you will find it there, (Provided you have created one as I mentioned earlier). Do not change anything on that screen and click ‘Next’ button. The following figure explains this screen.



5. On the next screen, you will be asked to select your database objects. For now, just check tables, and click finish button. Consider following figure.



6. A new window opens, that will show your tables in the database, since we only added tables.



Now, when you have done this, come back to our class that contains the main method. This will be our first example of this code. Have a look at our first example.

Example1

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data.Entity;
using System.Data.Objects;
using System.Configuration;


namespace CSharpTutorial
{ 
    class Program
    {
        public static void Main()
        {
            var connection = ConfigurationManager.ConnectionStrings["SchoolEntities"].ConnectionString;

            var EFContext = new ObjectContext(connection);

            EFContext.DefaultContainerName = "SchoolEntities";

            ObjectSet<Student> students = EFContext.CreateObjectSet<Student>();

            Console.WriteLine("\nTotal Number of Students:" + students.Count());
            Console.WriteLine("********************");

            IQueryable<Student> stud = students.Where(s => s.ID > 1);

            Console.WriteLine("ID | NAME | AGE");

            foreach (Student student in stud)
            {
                Console.WriteLine(student.ID + "  | " + student.Name + " | " + student.Age);
            }
            Console.ReadLine();
        }
    }
}

The code in Example1 might look intimidating at first but it isn’t that difficult actually. We started by getting a connection string from our App.Config file. We need to find the entity connection string that contains database connection as well as information about the EDM which we have created. If you open your App.Config file, you would see something like this.

Code:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="SchoolEntities" connectionString="metadata=res://*/SchoolEDM.csdl|res://*/SchoolEDM.ssdl|res://*/SchoolEDM.msl;provider=System.Data.SqlServerCe.3.5;provider connection string=&quot;Data Source=|DataDirectory|\School.sdf&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

The App.Config file actually contains configuration information about the application. This is basically an XML file that mostly contains global data. You can see that it contains a tag <connectionStrings>. Since, we only had one EDM added in this application, we only have one connection. Though, you can simply copy and paste the connection string wherever you want but .NET Framework helps you here as well. .NET Frame contains a System.Configuration class that let you interact with the App.Config file. You will be required to add reference to System.Configuration dll and then you will have to use System.Configuration namespace in your code. Now come back to what the first line of code does. First have a look at it again:

Code:

var connection = ConfigurationManager.ConnectionStrings["SchoolEntities"].ConnectionString;

Look at the right side of the equals sign. Here ConfigurationManager class is basically referring to App.Config file and telling it that, from the connectionStrings tag which has name “SchoolEntities”, return the value of the connection string attribute of that tag. This way we can get the connection string of our entity data model.

But question here arises that why do we need entity connection string here? Remember that in case of LINQ to SQL, we used a DataContext object that gives us an entry point to LINQ classes. See this tutorial, In case of LINQ to Entity framework, we require an ObjectContext object to have an entry to the entity framework classes. The DataContext object required a data connection but an ObjectContext object requires an entity connection. It is for this reason that we have to write logic to get entity connection from the App.Config file.

Another important thing to specify here is that we also need to specify DefaultContainerName of the ObjectContext type. This is normally same (unless specified otherwise) as the name of the connection string which is “SchoolEntities” in our case. We specified that to our EFContext object of the ObjectContext type.

Now coming towards getting data from the table, In case of DataContext type, you call GetTable<Table>() method. However, in case of ObjectContext, you will have to call CreateObjectSet<Table>(). In order to get the data from our Student table and store it in ObjectSet type. We wrote following line of code in Example1.

Code:

ObjectSet<Student> students = EFContext.CreateObjectSet<Student>();

Here, the ObjectSet implements IQueryable interface and you can query over it using foreach loop. In order to show that how queries are executed over ObjectSet, we simply wrote a query which would return all those rows from Student table, having ID greater than 1. This is done in following line of code.

Code:

IQueryable<Student> stud = students.Where(s => s.ID > 1);

Then using foreach loop, we simply displayed the content of the data set, which contains record of all students except the student with ID one. The output of the code in Example1 is as follows:

Output1



In our last tutorial, we used DataContext object for LINQ to SQL, here we used ObjectContext object for LINQ to EF; there is a reason behind this. Apart from providing us with entry points to the databases by creating objects that we can execute our query upon, these objects keep track of any modifications that are made to the entities they control. Consider a scenario where we want to update the Age of a student with ID 3, we can make changes to the database via DataContext and ObjectContext objects. To see, how we can do this, have a look at our second example.

Example2

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data.Entity;
using System.Data.Objects;
using System.Configuration;

namespace CSharpTutorial
{ 
    class Program
    {
        public static void Main()
        {
            var connection = ConfigurationManager.ConnectionStrings["SchoolEntities"].ConnectionString;

            var EFContext = new ObjectContext(connection);

            EFContext.DefaultContainerName = "SchoolEntities";

            ObjectSet<Student> students = EFContext.CreateObjectSet<Student>();

            Console.WriteLine("\nTotal Number of Students:" + students.Count());
            Console.WriteLine("********************");

            foreach (Student student in students)
            {
                Console.WriteLine(student.ID + "  | " + student.Name + " | " + student.Age);
            }

            Student stud = students.Single(s => s.ID == 3);
            stud.Age = 15;
            EFContext.SaveChanges();

            Console.WriteLine("********************");
            foreach (Student student in students)
            {
                Console.WriteLine(student.ID + "  | " + student.Name + " | " + student.Age);
            }
            Console.ReadLine();
        }
    }
}

Have a look at the above code. First we have simply displayed all the contents of the Student table from the database, using ‘students’ ObjectSet object. We done this to show the age of all the students and you will see that firstly, the student will have age ‘11’ as we specified in the database.

The magic begins with these lines:

Code:

Student stud = students.Single(s => s.ID == 3);
stud.Age = 15;
EFContext.SaveChanges();

What we have done in above lines of code is that we first got record of the student with ID 3. We have done this by calling ‘Single’ query operator over the ‘students’ ObjectSet type. The ‘Single’ operator is perfect to use when you want to fetch a single record based on some condition. We created a Student class stud, and stored the record of the student with ID 3 in this stud object.

An interesting thing to note here is that EFContext object which is of type ObjectContext, keeps track of all the entities that are referring to the database. So, when we store a record in stud object of type Student, the EFObject will store the information that this object is also interacting with the entity framework store model. In the next line, we stored value 15 in stud.Age variable. Finally, we called SaveChanges() on the EFContext object. When SaveChanges is called on the EFContext object, it will check that if any entity has made any change to the underlying database, it will find that stud has updated the Age, the EFContext will save changes and the change will be made in the underlying database.

Now if you again display all the records of the Student table by iterating over the Students ObjectSet object, you will see that, the student with ID, 3 have an updated Age i.e. 15. The following is the output of the code in Example2.

Output2

Associations using LINQ to Entity Framework

In this section, till now we have been discussing databases that contains single table. We had a School database that contained a student table. In real life scenarios you might have such a huge database that contains hundreds of tables. I expect you have basic knowledge of database and have an idea that how tables are related to each other. Just to revise the concept, tables are related to each other via primary and foreign keys. A primary key is basically that field in a record by which you can uniquely identify that record and no two records in any table can have same primary key. This key acts as a foreign key in the table to which the table has relationship of one to many.

This concept is best explained with the help of an example. For instance in a School database, you have another table named Report. Report basically refers to exam report of a particular student. One student can have many exam reports. For example, there can be mid-term examination, final examination or any other academic report. But one report has to belong to one student. It is evident from this scenario that there exists a relation between the Student table and the Report table and this relation is one to many. Means, one student can have many exam reports.

In such scenarios where we have one to many relation, the primary key of the table which is on side 1 acts as a foreign key of the table which is on many side. For instance, the primary key of the Student table, which is ID, will be passed as foreign key to the Report table, we will show you how to do this, but first follow these steps.

1. Create a new table in the School database and name it Report.
2. Create following columns in the table.

• ID => (Primary Key of type bigInt)
• TotalMarks => bigInt
• ObtainedMarks => bigint
• Remarks => nvarchar
• StudentID => bigint.

We want that they primary key ID from the student table should act as the foreign key in the report table and it should be stored in the StudentID column of the Report table. We can define relationship between these two tables in following way. Have a look at 3rd step.

3. From the server explorer on the left, right click on the Report table and click “Table Properties” as shown in the following figure:



4. From the window that appears, click on the Add Relations from the options on the left, a window will appear. Fill the window exactly as in the following figure and then I will explain what is actually happening here.



In the above figure, first we have named the relationship StudentReport. You can give any name but I prefer to use this convention: Name of the table with primary key, followed by the name of the table with foreign key, in Pascal case. Update Rule and Delete Rule, I prefer them to be cascade because this way if an independent record is updated or deleted in one table, all the records depended on this record are also deleted. You can change these setting if you want. The next two options are self-explanatory. The left one is the table which contains the primary key and the right table contains foreign key. Our left table is Student since it contains the primary key ID while right table is the Report since it has a foreign key. Next we again have two options. On the left, we have to specify that which table column would act as a primary key. You can select ID from the drop down table because this is Primary key of the Student table. On the right, you will select StudentID from the list of columns because the ID from Student table will be stored as foreign key in StudentID columns. Click on ‘Add Column’ button below the options and you should see ID on the left and StudentID on the right in the “Selected Columns for Relations”. Click ok and the relationship would be added in the database.

5. Next, click on the SchoolEDM.edmx and right click on the SchoolModel.Store and click on the first option i.e “Update Model from Database.” You will see automatically generated relationship of one to many between the Student and Report tables, as shown in the following figure:



6. Next, right click on the Report table and click on show table data to view the data in the Report table. At the moment, there will no data in the table. Add some records Report table as shown in the following figure.



Now comes the concept of referential integrity. We said earlier that the StudentID will contain the ID of the student from the Student table. Therefore, if you try to add some value in the StudentID column of the Report table, which is not present in the ID column of the Student table, you will not be able to add that value. You can only add those values in the StudentID column of Report table that are already present in the ID column of the student table since a report has to belong to some students and you cannot add any report that doesn’t belong to any student.

Now come towards the code again. We can easily take advantage of these relationships between tables via LINQ. For instance, we can get all TotalMarks and ObtainedMarks of the student, named “Mark” along with remarks. In our next example, we are going to explain that how we can do this.

Example3

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data.Entity;
using System.Data.Objects;
using System.Configuration;

namespace CSharpTutorial
{
    class Program
    {
        public static void Main()
        {
            var connection = ConfigurationManager.ConnectionStrings["SchoolEntities"].ConnectionString;
            var context = new ObjectContext(connection);
            context.ContextOptions.LazyLoadingEnabled = true;

            ObjectSet<Student> students = context.CreateObjectSet<Student>();
            Student stud = students.Single(s => s.Name == "Mark");

            Console.WriteLine("All the reports of Marks\n");
            foreach (Report rep in stud.Reports)
            { 
            Console.WriteLine(rep.TotalMarks + " " + rep.ObtainedMarks + " " +rep.Remarks);
            }
            Console.ReadLine();
        }
    }
}

The process of establishing connection to the database and entity data model is similar as we did in first and second example. Focus on this line of code.

Code:

context.ContextOptions.LazyLoadingEnabled = true;

Though, entity data model automatically create relationships, but if you do not binding associated entities in your code you cannot access one entity from the other. At the moment, just keep in mind that the above line of code basically attaches associated entities with each other. If you remove this line of code, you will still be available to access the data in the individual entities; however you cannot access the content of one entity through the other entity.

Next, in our example, we have created a Student entity object which we have named ‘stud’ and in this object we are storing the record of the student whose name is “Mark”. We have simply use a Single operator to select the item from the data set where name is “Mark”. Now we have a student entity. Real magic begins now. According to our database schema, one student can have many reports and one report belongs to one student. This scenario is handled internally by entity framework.

Actually in our scenario, entity framework will internally create an EntityCollection class, which will hold the collection of all reports belonging to a particular student. Therefore, though we do not have any direct entity object to hold the reports in our class, but we can access collection of all reports that belong to a student through that student’s entity. It seems absolutely logical. A student having many reports, and if you have the record of that student, you can access all his reports. This is what we did in our code at following line.

Code:

foreach (Report rep in stud.Reports)
{ 
    Console.WriteLine(rep.TotalMarks + " " + rep.ObtainedMarks + " " +rep.Remarks);  
}

In the above loop we are telling the compiler that loop through all the reports of the student ‘stud’ and display the contents of the reports. Since, every student entity will contain collection of reports which implement the IEnumerable interface, we can enumerate upon that collection of report via the foreach loop. If you look at the output code, you will see that from all the reports that belong to “Mark”, total marks, obtained marks and remarks would be shown which our requirement was. The output of the code in Example3 is as follows:

Output3



In our previous example, we enumerated on all the reports of “Mark”, what if we want to do other way around. We have explained that how you can access reports via student entity, what if we want to access a student entity via report. For instance, we want to see who has got the highest obtained marks. First we will have to see all the reports to find highest obtained marks and then we print the name of the student who has got the highest marks. To see how we can do this via associated entities in LINQ, have a look at our fourth example of this tutorial.

Example4

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data.Entity;
using System.Data.Objects;
using System.Configuration;

namespace CSharpTutorial
{
    class Program
    {
        public static void Main()
        {
            var connection = ConfigurationManager.ConnectionStrings["SchoolEntities"].ConnectionString;
            var context = new ObjectContext(connection);
            context.ContextOptions.LazyLoadingEnabled = true;

            ObjectSet<Report> reports = context.CreateObjectSet<Report>();

            long max = reports.Max(s => s.ObtainedMarks);

            Report reps = reports.Single(s => s.ObtainedMarks == max);
            Console.WriteLine("Highest Marks are " + max + ", obtained by " + reps.Student.Name +".");
            
            Console.ReadLine();
        }
    }
}

Before, explaining the code, I would like to you to think for yourself that how this scenario can be implemented by entity framework. In Example3, our Student entity contained a EntityCollection class of type Report, and all the reports belonging to that student could be accessed via this collection.

So, how we would refer to Student entity from Report entity? Do you think, we need an EntityCollection class of type Student in Report entity so that we can refer to the student to whom the report belongs? The answer is a big NO; but why? The answer to this WHY lies again in the type of relationship. Students can have many reports, therefore we had a collection in Student entity, which could store all of those reports. But a report belongs only to a single student, therefore in Report entity, instead of having a collection of Student entities, we just have one Student entity to whom the report actually.

Now come back to our code. Consider the following line of code:

Code:

long max = reports.Max(s => s.ObtainedMarks);

This line basically gives us maximum obtained marks. We have an ObjectSet reports, we called ‘Max’ operator to that and passed a predicate which would return us maximum ObtainedMarks. Remember, in our database, we specified the type of ObtainedMarks as ‘’bigint’, so if you try to store the resultant value in integer, the compiler will prompt you to use a ‘long’ data type which shows that how well LINQ enforces static type safety.

Next, in our code we declared a Report type object which we named ‘reps’ and in this report type object we stored the report whose obtained marks matched to that of maximum marks. Though there can be more than one reports with same maximum marks, we can use ‘’Where’ operator in that scenario. But here in this case we use Single operator which would give us one report in which the obtained marks are maximum. This is done in following line of code

Code:

Report reps = reports.Single(s => s.ObtainedMarks == max);

Now, from this reps object we can easily refer to the Student entity, to which this report belongs, we done in this in the following line of code:

Code:

Console.WriteLine("Highest Marks are " + max + ", obtained by " + reps.Student.Name +".");

Look how simple it is to get the the name of the student to which this report belongs. The output of the code in Example4 is as follows:

Output4



In the output, we simply displayed the obtained marks and the name of the student which got highest marks. Like simple queries and sub-queries, both LINQ to SQL and LINQ to EF follow the principle of deferred execution and unless numerated or accessed, actually doesn’t execute.

Entity framework itself is an extremely vast concept and cannot be covered in single article. You can find hundreds of website devoted solely to entity framework and its features. People have written heave books on entity framework, let alone .NET and LINQ. However, the purpose of this article was to introduce you to LINQ to SQL and LINQ to entity framework. But, we also covered some additional topics in this article. I showed you that how you can create tables in SQL Server compact using VS2010. And apart from creating tables, we learned that how we can define relationships between them. And finally we execute LINQ to EF queries to achieve desired result. I would advise to try insert & delete queries using LINQ, you can use InsertOnSubmit and DeleteOnSubmit data methods of the context object to achieve these results. For more interesting and useful C# tutorials, keep visiting this site.