Zeeshan Hirani

Configuring Existing DataBase with Many-to-Many using Code First

2010-12-09T02:33:45Z

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...(read more)

Builder Methods to create dynamic esql queries

2010-12-01T05:16:55Z

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. Builder methods allow you write sections of code using esql like a dynamic where clause, orderby clause or returning...(read more)

Filtering and Ordering Include

2010-11-13T03:25:16Z

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. Suppose you have a model consisting of Customers and...(read more)

Binding Stored Procedure Result from Entity Framework to ObjectDataSource Control

2010-11-03T03:11:23Z

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 1. Bring the Stored Procedure into the storage model. 2. Import the stored procedure into conceptual model. This will result in a method getting...(read more)

Using GetObjectByKey to short circuit database call

2010-10-21T22:16:53Z

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 If you execute the above code 5 times with the same object context, EF will execute the same query 5 times. In Linq to sql, once you fetch an entity once, next time you retrieve it using First Or Single, it will check the context first and if it finds the entity in the context it will never hit the database. This feature is useful in...(read more)

Inserting,Updating and Deleting entity using Stored Procedures

2010-10-14T06:07:27Z

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. Suppose we have a model containing Article entity as shown below. You can by default use the dynamic insert,update and delete statements EF generates to insert the article...(read more)

Upgrading Independent Association to Foreign Key Association

2010-10-13T05:15:18Z

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. In v1 of EF, there is only 1 type of association called Independent Association. Independent association is an association which is tracked separately...(read more)

Thanks EF4. Now i can put my orderby clause anywhere

2010-10-10T20:28:39Z

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. In the code below we are returning products sorted by their unit price. Notice in our query, order by is not...(read more)

Watch out for StartsWith when upgrading from .net 3.5 to 4.0

2010-10-07T03:51:16Z

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. When you run the above code, you get 0 records back. If you look at the sql generated for the above query, it uses CharIndex...(read more)

Which one is faster SingleOrDefault or FirstOrDefault

2010-09-20T18:32:24Z

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. When you use FirstOrDefault, you will get the first item that matches a given criteria. When you use SingleOrDefault, you are saying that there can be only one value that matches the criteria...(read more)

Lazy Loading Related Properties

2010-08-06T04:37:06Z

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. Let’s say on the user interface we are displaying Officer information and based on some action on the user interface...(read more)

Removing entity from a Related Collection

2010-07-23T16:13:46Z

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. Problem: You have an existing Phone entity that you want to remove from the customer’s Phone collection So let’s see how someone just walking into EF does it and assumes that code should works. In the above code, I am creating a new customer and phone entity...(read more)

Setting Entity Key for a view

2010-07-06T15:14:55Z

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. When I import the view I get the following entity on my model. If you look at the above entity carefully, you would notice EF has marked CustomerId,FirstName,LastName and FullName as the entity key. The entity key EF determines is not random. EF marks all the non null columns as entity key. There...(read more)

Search for an entity in your Entity Data Model

2010-07-05T23:07:30Z

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...(read more)

Watch out for let operator in linq to entities

2010-06-14T16:27:49Z

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...(read more)