Showing posts with label Entity Framework. Show all posts
Showing posts with label Entity Framework. Show all posts

Wednesday, February 16, 2011

Inheritance in Entity Framework - 1

Types of inheritance in Entity Framework

For beginners, please review: Entity Framework Basics
In this blog, I will explore on how to use inheritance in Entity Framework as a part of Advanced Data Models. Basically there are 2 models of Inheritance in Entity Framework and they both actually depend on the database design:

Table-per-Hierarchy Inheritance:
One table in storage schema to maintain data for all the types in an inheritance hierarchy. So, basically there is only one table in database, but different types in Entity model that inherits from a base class and all mapped to that table.

Table-per-Type Inheritance:
Separate table in storage schema to maintain data for each type in the inheritance hierarchy. That means there might have several tables with one-to-one relationships. Each table is mapped to single Entity in the model. But there is a base Entity that is mapped to the very base table.

We will look into Table-per-Hierarchy Inheritance in this blog with example. Generally, Table-per-Hierarchy is type inheritance which is having one table in Database but in Conceptual model of Entity Framework, the Entities are inherited from base Entity. For ex.: Consider real life development scenario, we often have a single table in Database having all the master data in the application ex.: Country, State, City, etc.
Consider below table which will hold all the master data:



In the above table, we are going to have all the Master Table Data along with parent child relationship among them. But through EF - Table-Per-Hierarchy, we will expose all the Entities to the users providing a level of abstraction. Please refer below figure in Entity Framework:


For one table in Database, we will expose Country, State, City as separate Entities referring to same table. So, let see how to achieve the same in Entity Framework:
1. First Add New Model in your Project
a. Add New Item by Right Clicking Project,
b. Select Add
c. Add New Item and ADO.NET Entity Data Model



2. Entity Data Model Wizard will open. Select Generate from Database



3. Configuration your model and DB:



4. Select the Enums Table and Press Finish:




5. You will get the below Entity in Entity Model:



6. Now in Enum Table in Database, we have three Master Category naming Country, State and City having
a. EnumCategoryID = 1 represents Country
b. EnumCategoryID = 2 represents State
c. EnumCategoryID = 3 represents City
So, first add the Country Entity as shown in below figure:





Do same for State and City Entities:





Once all the Entities are added, the model will look like:



7. Defining Columns in derived Entities.
Now, ParentEnumID is key relating the related records in Enum Table. For ex.: Enum table will contain records of Countries, States and Cities. So, for relationship between Country, State and City, ParentEnumID will be used which will be CountryID in State Entity and StateID in City Entity. So, Add Scalar Property CountryID in State and StateID in City Entity.
In order to add new Scalar Property,
a. Select “State” Entity, right click
b. Click Add --> Scalar Property.
Please note that Scalar Property should have the same data type as that in Base Table.
In our ex.: ParentEnumID is int, so Country ID and State ID will be int only.
Once your columns are added the model should look like:



8. Define Table Mappings:
Earlier I mentioned that EnumCategoryID is used to identity each Entity namely Country, State and City. For example EnumCategoryID= 1 represents that Entity is Country. Moreover, we need to map CountryID in State entity and StateID in City entity to ParentEnumID in Enum entity. So, let us now add Table mapping.
For adding Table Mapping, select Entity, Right click and select Table mapping as shown in figure:



Then, for Country, add Condition EnumCategoryID = 1, as there would be no ParentEnumID for Country, we will leave it blank.



For State, add Condition EnumCategoryID =2, in ColumMappings map ParentEnumID to CountryID



For City, add Condition EnumCategoryID =3, in ColumMappings map ParentEnumID to StateID



Once you are done, please don’t forget to delete the ParentEnumID and EnumCategoryID property from Enum Entity as it is not needed anymore.

9. Define An Abstract Class:
As I mentioned earlier, for demonstration I need to set Enum Entity as Abstract. However you can still define a mapping for it instead of making it abstract. You should set an Base Entity as Abstract only if you know there will be no direct instantiation from that Entity. For the case here, I would be having only Country, State and City entities. There is no simple Enum.
To set an Entity as Abstract:
1. Right Click on the Enum Entity, select Properties form context menu.
2. From Properties window and Under Code Generation group, set Abstract property to True. Click Ok on the confirmation message that appears.

Conclusion:
Let see what is exacting happing in the Model by exploring Conceptul Model, Storage Model and Mapping Model:
a. Storage Model



b. Conceptual Model




c. Mapping Model:



Find the sample code. And in few days, I will publish post on using the Table per Hierarchy in the Application with CRUD operations and Table-Per-Type example and explanation.

Thanks,
Paras Sanghani


TablePerHierarchy

Thursday, January 6, 2011

Entity Framework vs. LINQ to SQL(edmx vs dbml)

Hi Friends,


This post is regarding the difference between LINQ to SQL vs. Entity Framework.
Both are introduced as latest technologies and at times a bit confusing when to use which. Entity Framework and LINQ to SQL have a lot in common but still different from each other in quite a few ways:

Entity Framework:
1. Enterprise Development
2. Works with Conceptual model of database
3. Works with all data sources
4. ".EDMX" is created while using Entity Framework

LINQ:
1. Rapid Application Development
2. Works with objects in database
3. Mainly woks with SQL Server
4. ".dbml" is created while using LINQ to SQL

Entity Framework is more targeted towards Enterprise Development where the schema is usually optimized for storage considerations like performance consistency and partitioning. Entity Framework is designed around exposing an application-oriented data model that is loosely coupled and may differ from the existing database schema. For example, you can map a single entity (class) to multiple or map multiple entities to the same table. Entity Framework has “.edmx” (ADO.NET Entity Model) file when added in the application.

LINQ to SQL mainly has features to support Rapid Application Development against SQL Server. LINQ to SQL allows you to have a strongly typed view of your existing database schema. You can build LINQ queries over tables and return results as strong typed objects. LINQ to SQL has “.dbml”(LINQ to SQL) file when added in the application. You can use LINQ to SQL by decorating the existing classes with the attributes.

Please review: Entity Framework Basics

Thanks,
Paras Sanghani

Wednesday, December 29, 2010

Entity Framework Best Practices

Dear Friends,


This post is regarding some of points to be considered while writing the LINQ Queries which may affect the performance alot.






For Beginners in Entity Framework, Please review: Entity Framework Basics

Kindly, find all the below points which can increase the performance:

Compiled queries
When you have an application that executes structurally similar queries many times in the Entity Framework, you can frequently increase performance by compiling the query one time and executing it several times with different parameters. For example, an application might have to retrieve all the customers in a particular city; the city is specified at runtime by the user in a form. LINQ to Entities supports using compiled queries for this purpose.

use Compiled queries to amortize the overhead inherent in SQL generation.
The CompiledQuery class provides compilation and caching of queries for reuse.
For Example:
// Compiled Query
public static Func> _statesByCountry =
CompiledQuery.Compile((Entities db, int CountryID) => db.States.Where(s => s.CountryID == CountryID));

//Using Compiled Query
public virtual IEnumerable GetStatesByCountryID(int countryID)
{
return _ statesByCountry (Context, countryID).ToList();
}


Select N+1
Select N + 1 is a data access anti-pattern where the database is accessed in a suboptimal way. Suppose that want to show all Sites Information from all schools. The native implementation would be something like:
var productQuery = from product in _ctx. Product
select Product;

foreach (Product product in productQuery)
{
         //lazy loading of comments list causes:
        // SELECT * FROM Sites where SchoolId = @SchoolId
        product.ProductDetails.Load();
       foreach (ProdutDetail productDetail in product.ProductDetails)
       {
       //print comment...
       }
}

In this example, we can see that we are loading a list of Product (the first select) and then traversing the object graph. However, we access the collection in a lazy fashion, causing Entity Framework to go to the database and bring the results back one row at a time. This is incredibly inefficient.
The solution for this example is simple. Force an eager load of the collection using the Include method to specify what pieces of the object model we want to include in the initial query.
// SELECT * FROM Product JOIN ProductDetail ..
var productQuery = (from product in _ctx.Product.Include("ProductDetail")
                               select product);

foreach (Product product in productQuery)
{
             // no lazy loading of comments list causes
            foreach (ProdutDetail productDetail in product.ProductDetails)
            {
             //print comment...
            }
}
In this case, we will get a join and only a single query to the database.
Note: this is the classical appearance of the problem. It can also surface in other scenarios, such as calling the database in a loop, or more complex object graph traversals. In those cases, it is generally much harder to see what is causing the issue.

Avoid too many joins
Queries with too many joins might be a performance problem.
Each join requires the database to perform additional work, and the complexity and cost of the query grows rapidly with each additional join. While relational database are optimized for handling joins, it is often more efficient to perform several separate queries instead of a single query with several joins in it.
For OLTP systems, you should consider simplifying your queries or simplifying the data model. While we do not recommend avoiding joins completely, we strongly discourage queries with large numbers of joins. Another issue to pay attention to is possible Cartesian products in queries contains joins, it is very easy to create such a thing and not notice it during development.


Avoid Too Many Database Calls Per Session
One of the most expensive operations that we can do in our applications is to make a remote call. Going beyond our own process is an extremely expensive operation. Going beyond the local machine is more expensive still.
Calling the database, whether to query or to write, is a remote call, and we want to reduce the number of remote calls as much as possible.
There are several reasons why this can be:

  1. A large number of queries as a result of a Select N + 1
  2. Calling the database in a loop
  3. Updating (or inserting / deleting) a large number of entities
  4. A large number of (different) queries that we execute to perform our task
For the first reason, you can see the suggestions for Select N + 1.
Calling the database in a loop is generally a bug, and should be avoided. Usually you can restructure the code in such a way that you are not required to call the database in that way. All of the above points must taken into consideration while writing the Complex LINQ Queries. 

 


Thanks,
Paras Sanghani

Tuesday, November 16, 2010

ADO .NET Entity Framework

Hello Friends,

For Entity Framework Beginners, please review: Entity Framework Basics

We will have brief introduction on the Entity Framework in .Net Framework 4.0.
Let start with
Entity Framework Over View:
The Entity Framework is the new concept introduced by Microsoft in .Net Framework which allows developers to create the DataAccess applications by programming against a conceptual application model which is logical model instead of programming directly against a relational storage schema or Database. The goal is to decrease the amount of code and maintenance required for data-oriented applications. Basically ADO .NET Entity Framework divides the database model into three sub-models viz:
1. Conceptual Schema Definition Language(CSDL)
2. Store Schema Definition Language(SSDL)
3. Mapping Specification Language(MSL)
Before discussing further about the three sub models lets discuss why Entity Framework is a better option than the traditional approach.

Entity Framework Vs Traditional ADO .NET:One can write code against the Entity Framework and the system will automatically produce objects as well as track changes on those objects and simplify the process of updating the database. The EF can therefore replace a large chunk of code a developer would otherwise have to write and maintain. Further, because the mapping between the objects and the database is specified declaratively instead of in code, if there is a need to change the database schema, a developer can minimize the impact on the code he has to modify in the applications--so the system provides a level of abstraction which helps isolate the application from the database. Finally, the queries and other operations written into the code are specified in a syntax that is not specific to any particular database vendor--in ADO.NET prior to the EF, ADO.NET provided a common syntax for creating connections, executing queries and processing results, but there was no common language for the queries themselves; ado.net just passed a string from the program down to the provider without manipulating that string at all, and if there was a need to move an app from Oracle to SQL Server, one would have to change a number of queries. So the developers must become SQL experts to build advanced queries. With the EF, the queries are written in LINQ or Entity SQL and then translated at runtime by the providers to the particular back-end query syntax for that database.
Together with LINQ to SQL (L2S), LINQ to Entities (L2E) and EF are currently the best data access API that Microsoft offers. They are way better than 'traditional' ADO.NET for most scenarios.

Now let’s discuss the three sub-models:
CSDL :
CSDL is the Entity Framework's implementation of the Entity Data Model. It is an XML based language that describes the entities, relationships, and functions that make up a conceptual model of a data-driven application.

CSDL Specification:Conceptual schema definition language (CSDL) is an XML-based language that describes the entities, relationships, and functions that make up a conceptual model of a data-driven application. This conceptual model can be used by the Entity Framework or ADO.NET Data Services. The metadata that is described with CSDL is used by the Entity Framework to map entities and relationships that are defined in a conceptual model to a data source. CSDL is the Entity Framework's implementation of the Entity Data Model.
In an Entity Framework application, conceptual model metadata is loaded from a .csdl file (written in CSDL) into an instance of the System.Data.Metadata.Edm.EdmItemCollection and is accessible by using methods in the System.Data.Metadata.Edm.MetadataWorkspace class. The Entity Framework uses conceptual model metadata to translate queries against the conceptual model to data source-specific commands. In the .edmx file, under the following tag one can view the CSDL contents:

SSDL :It is a XML based language that describes the storage model of an Entity Framework application. The Entity Framework uses storage model metadata to translate queries against the conceptual model to store-specific commands. For more information on SSDL visit the link:
SSDL Specification :Store schema definition language (SSDL) is an XML-based language that describes the storage model of an Entity Framework application.
In an Entity Framework application, storage model metadata is loaded from a .ssdl file (written in SSDL) into an instance of the System.Data.Metadata.Edm.StoreItemCollection and is accessible by using methods in the System.Data.Metadata.Edm.MetadataWorkspace class. The Entity Framework uses storage model metadata to translate queries against the conceptual model to store-specific commands.

MSL :It is an XML-based language that describes the mapping between the conceptual model and storage model of an Entity Framework application.
MSL Specification :Mapping specification language (MSL) is an XML-based language that describes the mapping between the conceptual model and storage model of an Entity Framework application.
In an Entity Framework application, mapping metadata is loaded from an .msl file (written in MSL) at build time. The Entity Framework uses mapping metadata at runtime to translate queries against the conceptual model to store-specific commands.




Thanks,
Paras Sanghani