Archives
-
Configuring Existing DataBase with Many-to-Many using Code First
With CTP5 of Code First, you can easily map existing database schema to classes in your project. It allows you to configure what class and property gets mapped to what table and column in the database. Code First gives you two options to configure the mapping. You can either use DataAnnotation such as attributes or use the fluent api to explicitly control every single detail about the mapping. In the existing database schema, I had a many-to-many table and I was wondering what would be the cleanest way to map that to an association in my code first. Let’s see an example.
-
Builder Methods to create dynamic esql queries
In the current project, I am working on, I make use of esql heavily. One of the reason is because I am still using EF v1 which has limited support for linq queries. The second reason is, if you have dynamic queries which are build on what user selects from the search criteria, esql makes the job much easier. This is why Entity Framework supports the concepts of using builder methods.
-
Filtering and Ordering Include
Lot of times in your application, you have a requirement to eagerly load related collection but you want to load the collection partially and also have the data sorted when before it is materialized at the client layer. Currently in EF, there is no direct way to filter and sort and Include but there is a workaround. In this blog post, I will show you how you can use anonymous type to paritally load a collection in the correct order you desire.
-
Binding Stored Procedure Result from Entity Framework to ObjectDataSource Control
Not too long ago someone asked me how can they bind data returned from stored procedure to a gridview control. There are several ways to achieve that. You can either use EntityDataSource or ObjectDataSource Control. I find it easier to use ObjectDataSource control because it allows me to encapsulate logic inside my classes. The basic steps are as follows
-
Using GetObjectByKey to short circuit database call
There are several ways to query for a single entity using Linq such as First or Single. For instance in the code below we are retrieving customer based on CustomerID
-
Inserting,Updating and Deleting entity using Stored Procedures
If you have used entity framework with stored procedure, you would realize it has a great designer and runtime support. Yes there are few gotchas and loose ends that needs to be fixed but it does the job 99% of the time. In this blog, I will show you how to use stored procedures to perform crud operations on a single entity.
-
Upgrading Independent Association to Foreign Key Association
In EF when you want to define a relationship between two entities, you typically use an association. An association can be many-to-many, 1.. 0..1, many-to-1 etc. Typically you would have tables in the database which are related to each other using foreign key constraints. When you import those tables in Entity Framework, EF would create an association.
-
Thanks EF4. Now i can put my orderby clause anywhere
Well most folks wouldn’t have a clue as to what the title of my posting says. This is a problem that stems from EF v1 but is now solved for the most part. in EF4.0. In version 1 of EF, it was recommended that you put your order by operation as the last operation in the query. If you do not put order by as the last operation in the query query, order by would be ignored in some cases.
-
Watch out for StartsWith when upgrading from .net 3.5 to 4.0
Currently I am working on .net 3.5 project so i am having to live with Entity Framework. v1. Anyways i was working on a linq query that was making use of StartsWith operator. For some reason based on which page you are coming from the value passed in to the StartWith was sometimes empty. To be more clear let’s look at a sample code to understand how my scenario looked like.
-
Which one is faster SingleOrDefault or FirstOrDefault
Generally, I don't see too much performance problem whether you use Single Or First. However if you have a table with lots of columns like 300 you might notice slight improvement if you use FirstOrDefault. Before we go deeper into the performance difference let’s understand how they are semantically different.
-
Lazy Loading Related Properties
Lot of times in our application we do not perform eager loading on related entities because we don’t need to display them on the UI unless some action occurs. So we fetch those entities lazily only when we need them. However have you ever felt the need to lazily fetch several related entities? Suppose we have a model consisting officers who gave tickets to Drivers as shown below.
-
Removing entity from a Related Collection
I have seen this misunderstanding starting to popup more often on how to delete an entity from a related collection. Suppose we have the following entity data model where a Person has many Phones identified by 1-to-many association as shown.
-
Setting Entity Key for a view
When you import a database view using Entity Data Model wizard,you will get an entity that has the same number of columns and data type as the view does.Suppose I have the following view in my database.
-
Search for an entity in your Entity Data Model
Last week i discovered a feature of Entity Data Model which i have not seen extensively being promoted. I accidentally run into it because of the size of my model. If you have a large model and want to search for an entity, it becomes tedious to zoom in and out and scroll up and down to find your entity. In Entity Framework 4, on the model browser there is a new search option that allows you to search for any artifact on your model, whether it be a property, association, column, entity. Below are some ways to search for any artifact on the model.
-
Watch out for let operator in linq to entities
LINQ has a really cool operator call Let that lets you declare variables inside the query. The benefit i find in using let is, it makes my query much easier to read because using Let keyword i can create a variable and assign it a calculated value. Then later in my query i can test my value against that variable. In LINQ to entities if you are using let keyword, you want to be aware of how the sql is generated. There are cases where let keyword introduces extra or repetitive joins that are un-needed. Let’s see an example of where let introduces extra join when the linq query is translated. In the figure below I have a SalesRep entity that has an optional 0..1 association with RepSalesStatus. RepSalesStatus table in the database contains the totalsales a given rep has done. Since the association is optional there could be reps in our database that have not done any sales as yet.
-
Small SQL improvement in Table Per Hierarchy
Well the title of my post is somewhat disguising the actual point which I wanted to communicate. There is basically two important things that i wanted to talk about in this posting. First a slight improvement in T-SQL generation for Table Per Hierarchy from version 1 of EF. Secondly an existing smart T-SQL generation for Table Per Hierarchy which had always been there but i never noticed it. Let’s dwell into some of these features by starting with an existing model that i have already created.
-
Contains is smart enough to check for null
Starting with Entity Framework 4.0 you can use Contains clause in your linq query to check if the column value matches any values in a given list. Suppose we have a model with Product and Category as shown below.
-
How to share common fields between two entities that map to different tables
The title is a mouthful so let me explain what we are trying to solve. Suppose we have two tables in the database that have common fields. When we import the tables into Entity Data Model, we want to create a base entity to contain the common fields and let other entities derive from it. Since the base entity does not map to any table in particular in our database, we want to make base entity abstract. If you have reached this far and understood what the problem is, then follow along to know to solve it!
-
Entity Framework 4.0 Recipe book is officially released!
I am very happy to announce that Entity Framework 4.0 Recipes book by Apress is officially released and is available for purchase from Amazon and other online book retailers. You can get your copy from Amazon at the address below.
-
How to perform left join in linq to Entities
To perform left outer join in a linq query, you would normally use DefaultIfEmpty operator. DefaultIfEmpty opreator basically gives you a default instance of an object when there is no related record found. In version 1 of Entity Framework, DefaultIfEmpty was not supported, therefore you had to apply different techniques to get around this problem. I am very happy to see that DefaultIfempty is finally supported in Entity Framework version 4. Let’s walk through an example of how to use DefaultIfEmpty operator to get a left join.
-
Stored Procedure returning entities and output parameter
If you have a stored procedure that returns a collection of entities and also an output parameter, you must iterate through the resultset before the output value is available on the client. Accessing the output parameter value before looping through the result would give you null value. Let’s walk through an example to see how we can correctly access an output parameter value from a stored procedure.
-
Entities with complex Type properties cannot be returned from Stored Procedure
Complex Types have been around since version 1 of Entity Framework. However the designer had no support for creating, modifying and returning entities with complex type. If you used complex type by editing the xml manually, you were not able to open the edmx file in design view. Although version 4 of Entity Framework made remarkable improvements in this area and working with complex type in designer is seamless, there is still a feature for complex type that did not make it into this release. If you have an entity that has a complex type property and you are returning that entity from a stored procedure, you will get a runtime InvalidOperationException. Although the designer would be happy to let you map this feature, at runtime you will see this exception.
-
Filtering Entities with foreign key value
Entity Framework 4.0 supports two two types of association, foreign key association and independent association. Foreign key association which is new allows you to modify/filter an entity using either navigation property or foreign key column exposed on the entity.
-
Calling User Defined Database Function From LINQ
Entity Framework 4.0 allows you to call a database function both in an esql query and linq to entities. However database function can only return scalar types. Future version would support table valued functions. To use a database function in a query, you have to perform the following steps.
-
Complex Type and POCO
Change tracking in poco objects can be accomplished in two ways. If you mark your properties as virtual, EF will create a proxy object for your entity and any property changes will be notified to the ObjectStateManager immediately. However bare bone poco class has no mechanism to inform ObjectStateManager of changes occurring. When poco object is attached to the context, EF will take a snap shot of the current property values and when DetectChanges is called either explicitly or implicitly when SaveChanges occurs, EF will walk the object graph looking for changes that have occurred and modify the state of the object correctly in the state manager. This form of change tracking is called Snap Shot based change tracking where EF takes a snap shot of the original object and then a modified object to find the difference. Let’s look at example of a model consisting of Employee entity with Name property as complex type.
-
Entity Framework 4.0 class at Collins Community College
Today I presented at Dallas C# user group. The audience was great with lots of interesting question. I met some new faces which i never saw. I saw substantial women participation in the group as compared to previous times which indicates that more women are in technology then you would think. In fact my old friend Teresa was there too(thks for showing up girl!)
-
I finally became MVP
I am excited to announce that i got awarded MVP title for the first time. Last year, i have spent good amount of time in the community by speaking in user groups, webcasts, writing book and helping developers on the Entity Framework Forum. With full time school and plus home work, it is tough to take time out for anything else. Hopefully i will continue to share my knowledge in the community and speak more often.
-
Entity Framework Videos
Not too long ago, I gave two presentations on Entity Framework 4.0 covering new features like complex type, functions, stored procedures, POCO, Self Tracking Entity and some other features. Shawn was kind enough to record the videos for INETA.
-
Modifying Self Tracking Entity on the server
Suppose you are working with a console application on the server and you have created an entity data model with applicant entity as shown below.
-
Loading recursive queries
Entity framework supports self referencing entity where an entity has a a reference to itself. A common example of that would be a category with subcategories. In the database you would typically see a category table with a ParentCategoryId column which represents the parent category of the current category. Figure below shows the Category table in the database.
-
Using Self Tracking Entity to retrieve and update
In this tutorial I will explain how to use Self Tracking Entity to retrieve entities from a WCF service, modify the object graph on the client side and then send those changes back to the server in a clean way.
-
Returning Maximum value from 1-to-many association
Today I was working with two entities and i wanted to return the maximum value in the many table for each record in the parent table. There are several approaches to returning the desired data but since my tables had lots of records i wanted to explore different queries and compare which one generates a better sql and gives me better performance.
-
Registering with AssociationChanged event on POCO with change tracking proxy
Entity Framework 4.0 allows you to use POCO objects for your entities defined on the model. Suppose one of the POCO entity has a collection property and you want to be notified when a new entity is added or removed from the collection. How do we get notified when the collection is changed? Well if your poco objects supports change tracking proxy, you can be assured that Entity Framework will initialize your collection to EntityCollection. You can use EntityCollection’s AssociationChanged event to find out what got added or removed. EntityCollection has been around since version 1 and it is currently the collection that is used if you use the default code generation. Some of the benefit of using EntityCollection includes.
-
Placing Model in separate Assembly
Today, I was cruising the forum and i ran across an interesting post where the poster wanted to place the model and ObjectContext in different assemblies. Imagine you have a customer table and you created the Entity Data Model with Customer entity. You wanted your tiers to be as follows