Archives
-
Linq To SQL Optimizes Eager Loading
If you have read my previous blog posting about the Load operator in entity framework and how it works in terms of lazy loading object regardless if it object has been tracked earlier, you must be keen to know how does this behavior work with Linq to SQL. I must say Linq to SQL has always surprised me in terms of how efficient it is in terms of dealing with retrieving data from the database. When you lazy load a particular entity reference, Linq to SQL will first track its tracking repository to see if it can find the object there. If an object is found it by passes database and simply returns the object from its cache. This is an optimized behavior as compared to how entity framework tackles this problem. For example if I have much of orders and I want to lazy load its customer, Linq to SQL will not make a database call for every Order to get its customer. If those orders belong to a total 5 distinct customers, than only 5 database calls will be made. Below is an example that shows this behavior.
-
Avoiding Roundtrip with Load in Entity Framework
If you have queries in your classes that you are reusing across your entire business layer, it becomes really hard sometimes to do eager loading of certain child entities on those queries. For example, if you have method that runs a complex linq to entity query to return you a collection of Orders. What if in certain scenarios of your application, you would like to retrieve Customers for those orders as well. Either you can create another method just like your previous method which has that complex query duplicated but along with that query you support the concept of eager loading of Customer entities as well. Other option you have is if the orders returned from the method are not too many and in manageable size, than you may end up wanting to reuse the method and incur the cost of lazy loading of the Customers. Below is an example that shows how I would lazy load Customers for an Order.
-
Comparing Dates in Linq To SQL
If you want to compare dates in Linq to SQL query, you are free to use normal operators available in C# and Linq to SQL provider will translate the date comparison into appropriate SQL which makes uses of date functions available on SQL server. To demonstrate the usage of Date functions let's look at an example.
-
Many To Many Mappings in Entity Framework
So far in my previous blog postings, I have discovered goodness with linq to SQL as I travel the path of migrating from linq to SQL to entity framework. This is not to deny that entity framework also has plus points which cannot be ignored. Among them is support for many to many relationship. Many to Many relationship is a concept that is very common in most OR mappers so one would wonder why it did not make it into the service pack release for sp1 for linq to SQL. I am sure Danny Simmons will have a better answer to this question than me.
-
Entity Framework Does not support object comparison in Queries
As I continue to migrate my linq to SQL code over to entity framework, I am discovering more constraints that I never faced working with linq to SQL. For instance in one of my linq query, I was able to do object comparison to apply filter to queries. For example, If you wanted to retrieve customers who are in the city of London and also customer that you currently hold in your hand and find out the count of customers that meet this criteria or probably find out how much total sales those customers have given us, you probably want to perform this query on the database. In the past I could apply object comparison in my query rather than applying comparisons based on column values. You could actually use column values to dictate your filters, but sometimes being explicit on how you are applying a filter puts noise in your code as compared to just saying make sure this customer is also part of the filter.
-
Different Implementation of Cast in Linq To SQL And Entity Framework
To set the stage of what I am going to blog about, I would recommend that you read by earlier blog posting here where I have talked about how the cast operator works. In short when you use cast operator with Linq to SQL to fetch a particular concrete type, you end up making a call to the database that fetches all records and than cast is performed in memory to cast the objects to the type passed in as a generic type. If the object cannot be cast to the correct type, the object is assigned a null value. The implementation is very similar to as operator in C# where if the object cannot be cast to right type, the reference of the object is set to null. Important point to understand is, even though your Linq query may not give the results you desire, but the query wont crash. However if you were to write the same query in Linq to entities or entity framework, you get a runtime exception stating that a cast is not valid. Below is an example that shows the behavior?
-
Entity Framework does not support Custom Properties in Queries
As I am starting to learn more and more, I think linq to sql first release is much more mature that v1 release of entity framework. For instance, in linq to sql, if I would create custom property in my partial class, I could actually use that custom property in the projection of my query. One of the obvious reasons for creating custom property is to reuse code functionality such as creating a property called Name which combines, FirstName and LastName. You really don't to write that code all over you class diagram. Exposing the function as a property and being able to use that property in a projection is a valuable functionality added to the framework. Below is the code that shows using custom property in a projection of a query.
-
Single Table Inheritance in Entity Framework
In this blog posting, I will do a walk through of how to apply single table inheritance using entity framework. Entity framework supports 3 different models of inheritance.
-
Changing Namespaces in Entity Framework
If you are like me, migrating to entity framework from linq to SQL, I must say you have been spoiled. What seemed to be very simple has gotten much more complex. I find that Entity framework solved my persistence ignorance problem but brought me much more complicated problems to deal with. Here is one that took ages to find out
-
MultipleActiveResultSets in EntityFramework
MultipleActiveResultSets was a feature introduced in the connectionstring with ado.net 2.0. In prior versions of ado.net, you can only execute a single query at a time on a particular open connection. When you set MultipleActiveResultSets to true, you can execute multiple commands on a single open connection, resulting in better performance. It's interesting that when you generate your linq to SQL classes, by default it does not make use of MultipleActiveResultSets in the connectionstring. However when you generate entities using entity framework, the connectionstring added to web.config or app.config has explicit setting for MultipleActiveResultSets = true to enable the feature. If you set MultipleActiveResultSets to false in the connectionstring most of the queries in the entity framework fail. It appears that you have to set MultipleActiveResultSets to true in the connectionstring if you want to use EntityFramework.
-
Lazy Loading Entities in EntityFramework
In my previous blog posting we learned how to eagerly load entities in entity framework. Eager loading causes lots of entities to be loaded ahead of time which may not be required and feasible in all scenarios. In those cases you can call Load Method on an entity or collection of entities to lazy load entities on demand. This ensures that you do not bring lots of data from database and only loading entities when you need them. The problem with this approach is, your querying process would be very chatty and you would end up making too many database calls. For example if you have collection of customers and you want to lazy load customer's address, than depending on the number of customers you have in your collection, linq to entities will make that many number of calls to get addresses for all customers in the list.
-
Eager Loading entities in Entity Framework
As I am starting to move forward with entity framework on all my projects, I am discovering new ways of writing queries that works with entity framework as compared to in the past with Linq To SQL.
-
Migrating from Linq To SQL to Linq To Entities
I am not sure if it is a sound idea to move all the stuff over to Linq to entities from Linq to SQL. However this current portion of the project that I had started few days ago felt like a really good place to test with Linq to entities. So quickly migrated over my code from Linq to SQL to Linq to entities. Surprisingly except for changing few namespaces and datacontext rest of my Linq queries just complied fine against Linq to entities. After compiling the code, I went ahead and tried to run the application and I met my first exception.
-
Constraints With LoadWith when Loading multiple 1:n relationships
This problem totally came as a surprise when one of our pages in production was taking too long time to load. Obviously to trouble shoot the problem, I opened up SQL profiler, started looking at the various SQL statements being sent to the database. I was surprised to see that LoadWith operator was not working as advertised when eager loading more than one, one to many relationship. To demonstrate the problem, let's start with a bare bone example where I eager load Orders for the customers in the city of London.
-
Peculiarity With Cast Operator in Linq To SQL
As I have mentioned in my previous blog postings, Linq provides a great querying model which hides the complexities of different domains such as Linq to objects, Linq to SQL and Linq to XML. However it is important to understand how a particular query behaves and runs on a particular domain.
-
Using Embedded Expressions in XML to Replace String.Format And StringBuilder
If you have been using StringBuilder or String.Format to build strings, they provide nice syntax to replace placeholders such as {0}, {1} etc with the values passed in the second parameter to the method. Although I have been using this syntax to print and create customized displays, I still feel that there are sometimes harder to read because of the noise the method and placeholders add to the code. With the awareness of XML into the vb language, you can leverage embedded expression to build customized strings from XML by calling the Value property on the XML literal. Below is an example that demonstrates this usage.
-
Creating XDocument using Data from Database
With Vb, building an XML Document on the fly from a list in the database is very simple. Vb supports the concept of embedded expressions which is similar to asp.net concept of embedding code expressions in aspx page. This allows you to build you XML file as it should look when its opened. XML literals also allows you to replace sections of the file with data from anything that is IEnumerable. Example below builds an XML document from a list in the database.
-
How To Make XElement An XDocument
This is a cool trick that I discovered in Vb when working with XML. Vb has full support for XML in the language so much so that you can type in XML write in the code and IDE would do color highlighting to differentiate it from the code in the page. It would also do correct indenting of the code so XML is more readable. By default when you type in XML in Vb.net code, it is inferred as XElement. Below example shows this behavior.
-
Assign DataLoadOptions just before executing the linq query
If you have been making use of DataLoadOptions to eagerly load 1 to 1 entity or 1 to many entities such as child collections; you have to make sure that you apply all these operations before assigning the DataLoadOptions to the DataContext. For example, when you use LoadWith method to Load Oders for a customer, you need to ensure that you call LoadWith method before you assign DataLoadOptions variable to DataContext. Any changes you make to the DataLoadOptions after it has been assigned will result in runtime exception by the compiler. Example below shows the exception raised by the compiler in an correct usage of the datacontext.
-
Linq To SQL Provider Rewriting My Queries!
From the title of the blog posting, readers must be thinking, I have already given away control of writing SQL to Linq providers. Now do I get to loose control over writing my Linq queries? Well in reality not. What I meant is Linq provider analyzes the queries and if it finds repetitive filters or duplicate checks or even for that matter finds that a query can be written efficiently based on the data available at runtime which was not be available during compile time, Linq providers will fix the query and translate the fixed version of the query to SQL to be sent to the database. Below is an example that demonstrates Linq provider's ability to modify Linq queries for better performance and less redundancy.
-
Linq To SQL Query plans not being reused
When you use Linq to SQL to apply filter, orderby or any other operator, Linq provider translates the query to SQL and sends it to the database. When the query is send to the database, SQL server determines if it can use an existing query plan for this query or create a new query plan. One of interesting point I discovered is, Linq to SQL query plans do not get reused. For instance if I write a Linq query to find customers with Contact Title of Sales Agent, SQL server would generate an query plan which you can see in syscacheobjects table. When I run the query again with a different Contact Title say, Accounting Manager, SQL server, instead of reusing the same query plan generates a new query plan. The problem with this is, dynamic queries have limited caching buffer and once it reaches it max size allocated for the dynamic query buffer, it would start to flush out the old query plans. Since this process would be happening so fast, that it would hard to reuse the same query plan again resulting in a new query plan being created every time. Secondly if you look at syscacheobjects table, there is another column called usecounts which tells how many times this query plan has been used. Most of the time with Linq to SQL query the usecounts never goes past one. Below is an example that shows different queries being send to the database using Linq to SQL.
-
Binding IQueryable Derived class to ListView Raises Exception
Linq to SQL supports single table inheritance. So if you have an employee table, you can store both Salaried and Hourly Employee in a single table. To identify which employee is what, you can use discriminator column. To map a table that contains multiple derived classes in Linq to SQL, you have to do 3 things. For each derived class, you have to specify what discriminator column to use. Secondly you have specify what value in the discriminator column identifies a particular derived class. Third you have to specify what should be the default derived class in the case where database does not define a value for the discriminator column. In the example below I have an employee class which has two derived classes, SalariedEmployee and HourlyEmployee.
-
IExecuteResult to return data from stored procedures
When a call is made to a stored procedure or a function that returns a scalar value, linq to SQL designer generated code calls ExecuteMethodCall which returns an object that implements of IExecuteResult. IExecuteResult interface has one method and one property. Below is the definition for IExecuteResult interface.
-
Difference between ExecuteMethodCall and CreateMethodCallQuery
If you drag stored procedures or functions on linq to SQL designer, the code generated to uses ExecuteMethodCall or CreateMethodQuery.
-
Using Stored Procedures In Linq
Using Linq to SQL, you can use stored procedure to return your entities. If you want to use the designer, you can go to the server explorer and drag a stored procedure on an existing entity. This would cause the Linq to SQL designer to generate code for calling a stored procedure that returns your entities. If the stored procedure does not return any particular entity, dragging the stored procedure on the designer would generate a class on the fly which would contain properties that match the columns your stored procedure is returning. If you are using SqlMetal utility to generate your entities, than add /sprocs option to indicate that you want code generated to call stored procs in your database. To better understand what code designer generates, let's take a look at the code generated when calling a simple stored procedure.
-
Using External Mapping File With Linq To SQL DataContext
By default when you use visual studio to drag tables onto Linq to SQL designer, you get table per entities generated in a single file. Those entities have attributes defined on them that defines how an entity is mapped to a table in the database. Although each of the generated entities are marked as partial, you never have to look at them because if you want to write code in those classes, you have the option to create your own partial class and put your logic in there. This makes your partial classes clean and devoid of any generated attributes which clutters up the code, making it hard to read. However using sqlmetal command line utility you have the option to move those attributes to an external mapping file and read the external mapping file at runtime as one of the parameters to the DataContext's constructor. Below are some of the examples of different usages of sqlmetal that provides various flexibility in code generation and gives control in how the code is generated.
-
Is Your Linq Query being executed on the database?
Depending on the approach you take when writing linq query, you may be surprised that some of the operations are getting performed in memory instead of the call being translated to sql and executed on the database. When you apply aggregate operators on association relationship on an entity in the context of a query syntax, the aggregations is performed on the database. However the same syntax when executed outside of the query syntax would force the entire association relationship to be brought from the database and aggregate operation performed in memory. In figure 30, I have two different version of the same query that returns the customerId, Total number of orders they have placed and TotalAmount they have spent on orders so far. In the first query, I am using association relationship Orders available on customer to get the total orders placed. To get the total amount spent, I first used the association relationship Orders on the customer and for each order, I navigate to its order Details association to calculate the cost of each Order. Since I am using association relationship inside of a linq query, the entire query is converted to sql and send to the database for execution. Second query in Figure 30, uses the same association relationship on the customer entity to perform calculations. Since the calculations are not part of an existing query, linq to sql has to bring all the orders for the customer and for each order bring down all its OrderDetails to calculate the Sum and Count operation. Bringing all the Orders and OrderDetails for a customer is an expensive operation which need not to be performed if all you are want ing to do is get the count and sum. Those operations can easily be done on the database. As a developer, it is important to understand the tradeoffs and know which option may be better suited for your scenario. For instance if you already have the order and OrderDetails for a customer in memory, than it may be more efficient to perform these operations in memrory instead of making a database call to the server.