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.
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.
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:
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.
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.
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.
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.
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.
The magic begins with these lines:
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.
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.
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.
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.
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.
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:
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
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.
Re: LINQ to SQL & Entity Framework Understanding Similarities and Differences
They are somewhat similar, and can be used in a very similar way, code-wise, but they have some important differences. Note that "LINQ" is not the same thing as "LINQ to SQL"; the EF also uses LINQ. Some notable differences are:
LINQ to SQL is largely SQL Server only, not so much by design as by implementation. The EF is designed to support, and does support, multiple DBs, if you have a compatible ADO.NET provider.
Out of the box, LINQ to SQL has a very poor story for DB metadata changes. You have to regenerate parts of your model from scratch, and you lose customizations.
The EF supports model features like many-to-many relationships and inheritance. LINQ to SQL does not directly support these.
In .NET 3.5, LINQ to SQL had much better support for SQL-Server-specific functionality than the EF. This is mostly not true in .NET 4; they're fairly similar in that respect.
The EF lets you choose Model First, DB First, or Code First modeling. LINQ to SQL, out of the box, really only supports DB First.
for more visit employmentstation.blogspot.in
|All times are GMT +5.5. The time now is 17:53.|