June 2008 - Posts
When you insert or update a record in the database, there are certain columns whose values are either generated because they are identity columns, or columns whose value is assigned by SQL server such as timestamp column. You could have triggers that update columns after an insert or update on the table. Those column values are not reflected in your entity. However linq to SQL offers different syncing options to sync properties on your entities with column values generated by the database. You can use AutoSync parameter on your columns and specify when linq to SQL should update those columns such as OnInsert, OnUpdate,Always,Default and Never. I have generated a sample class that show variety of syncing options that you can apply on your properties to sync your entities after insert and update of a record to the database.
Let's start with OrderId column. Since OrderId is defined as AutoGenerated key I am setting IsDbGenerated to true. Since the column value is going to be generated by the database, I need to tell linq to SQL to AutoSync the column. Therefore I am setting AutoSync.Default. The Default option means that if the column is marked with IsDbGenerated than update the column on the insert of an entity. If the column is IsVersion column like I have a timestamp column, than update the column on insert or update operation.
The Actual Column in our database is a calculated column that is obtained by multiplying DiscountPer and OrderTotal and dividing the results by 100 . I am setting the sync option to be always meaning always update the value of this column from what's in the database.
Since CreateDate is only assigned first time record is inserted, I am setting the AutoSync to Insert, to update the column only when the entity gets inserted.
Since Modified date is only set when the entity gets modified,I am setting the AutoSync to OnUpdate, to update the column only when entity gets updated.
Below is an example of inserting an instance of Order entity and than updating the entity to confirm that our auto sync parameters update our respected properties.
In the above code, after inserting the order entity in the database, I am printing the OrderId,Actual,CreateDate and ModifiedDate on the output screen. Notice on the insert our modified date is set to null because we have configured the column to be synced up when the entity gets modified. After printing the output, I update the DiscountPer column and send the changes to the database by calling SubmitChanges. This time our modified column reflects the correct value for ModifiedDate.
If you find that a particular query is hard to represent as a Linq query, you can consider other options such as writing your own SQL statements inside of a stored procedure or a function and than calling the stored procedure and function using Linq to SQL. You can also write dynamic SQL statements using the helper methods provided on DataContext. I am sure some of you may be thinking how is that new or different from ADO.net which always allowed executing dynamic SQL and getting a datareader or dataset back. Well you can achieve the same results by making use of ExecuteQuery<T> method available on the DataContext. ExecuteQuery method on DataContext does a lot more than simply executing a dynamic SQL. First it supports the concept of parameterized queries. You provide parameter like you provide parameters to string.format using placeholders which Linq to SQL converts it to a parameterized query. In traditional ado.net when you execute dynamic SQL, you either get a datareader or dataset. It had always been left to the developer to convert the datareader to customized business object. However ExecuteQuery takes in a generic type that tells the Linq to SQL that a particular SQL statement would return a strongly typed instance of the class passed in as a generic type. You no longer have to worry about converting datareader to object. There are some restriction that you must follow in order to accomplish proper mapping of your columns to properties on your object. First your query columns must contain the column that is defined as the primary key. Second the column names returned by the query should match with the property names defined on your entity class. If the column names do not match than you can use alias in t-SQL to match with the property name defined on your entity. Further more, Linq to SQL does not require that you return all the columns that map to all the properties on your entity. If your query is missing some column, Linq to SQL would assign default value to your property. Below is an example that illustrates some of this behavior.
In the above code, I am using placeholder to create parameterized query. Since the query is parameterized, I am not using a single quote to pass Seattle filter to my shipCity. In the query, I am only returning customerid and companyname from the customers table. Since Linq to SQL does not require that you bring all the column, only columns that match with the property name gets mapped. Therefore when I print the ContactName its value defaults to null because we did not pass ContactName in our query.
Linq to SQL support many aggregates such as Sum,Avg, Min, Max and few others but they may not execute on SQL server. Depending upon how you write your queries, you would be surprised the same syntax would actually bring the entire data from the server and execute the aggregate operations in memory. Below is an example where the entire query executes on SQL server and hence very efficient in terms of performance. While the same exact query just written in two steps causes the records to be fetched from SQL server and aggregate be performed in memory.
In the above code, I am demonstrating two examples for displaying the same data. Basically I am displaying the Company Name, TotalOrders and Total Quantity ordered. In the first query, the Count operation for orders and sum operations on the quantity for each Order Detail item gets executed on SQL server. This is because the Sum and Count aggregate operators are part of a bigger Linq query. If the aggregate operators are not part of the query, than Linq to SQL brings the data down and executes the aggregate operators on the in memory collection. Here is the SQL generated by the first query.
Looking at the above code you can confirm that our Count and Sum operations got converted into appropriate SQL syntax.
On the second query, I am first retrieving the customer and than Counting its Orders and also summing the quantity ordered for each Order Detail Item. The syntax is exactly the same as above query but since the aggregate operators are not part of the Linq query they use Linq to objects implementation. In order to execute get the Count of Orders, Linq to SQL brings all the orders for the Customer and Than Count operator is called on the Collection retrieved. To Sum the quantity, we are making seprate database calls to retrieve the OrderDetails for each order. Since we have 6 orders we are making 6 database calls to retrieve OrderDetails for 6 orders. Once we have retrieved the OrderDetails for all the orders, the sum operator is applied on the OrderDetails Collection. Here is SQL generated to bring the OrderDetails in memory.
If you have generated your Linq to SQL classes using visual studio Designer or used sqlmetal utility, you would notice that your entity classes implement two very important interfaces for change tracking called INotifyPropertyChanging and INotifyPropertyChanged interface. These interfaces belong to System.ComponentModel namespace and were introduced in .net 3.0 runtime. Although the use of these interfaces are not mandatory for your entity classes but having them make the change tracking process efficient and less memory intensive. If your entities do not implement Notification interfaces, Linq to SQL will keep track of two copies of your objects, one with the original values when you first fetched the object and second object which has changes applied to it. When submit changes is called, Linq to SQL will do a compare of those objects and send only the properties which has changed since the first retrieval. This process which keeps tracks of two copies of the object can cause memory issues when you are managing lots of objects. Therefore Linq to SQL also provides an option where it can allow entities to to participate in change tracking service. Entities can participate in change tracking service by implementing INotifyPropertyChanging interface and INotifyPropertyChanged interface and raise propertychanging and property changed event in the setter of the property. Let's look at some code below to see how our entities can participate change notification service.
In the above screen shot, I have limited my code to demonstrate how entities classes participate in change notification service when a particular property gets changed to when something gets removed or added to the collection. When we implement INotifiychanging and changed interface we automatically get two events called Propertychanging and PropertyChanged. The first time you request the object from the database, Linq to SQL checks to see if the object implements INotifiychanging and Changed. If it implements the interface than it registers with the two events in our Customer class. If our entity does not implement the interface, it create a copy of the object to be used later to compare it with the modified object. In my customer entity I have Name property. Every time Name property changes, in the setter of the Name property, I am raising Propertychanging followed by PropertyChanged passing the Name of the property that got changed. This notifies Linq to SQL that a property has changed.
For notifying when collection gets modified, in the constructor of the customer entity, I am creating an instance of orders entityset. Order entity set has constructor that takes two action delegates. Firsts action delegate gets called when an Order gets added to the orders collection. Second delegate gets called when an order gets removed from the order collection. I am representing both the delegates as simple lambda expressions that basically raises a changing and changed event to notify Linq to SQL that order collection has changed. In a more real world code, you probably want to do lot more stuff inside the add and remove action delegate.
By default Linq to SQL would lazy load all your child collections like when you have an instance of customer, its Orders are not loaded. ON traversing the Orders collection, Linq to SQL fetches the Orders for that customer. You can use LoadWith operator to eagerly load the Orders for the customer. But what if you also want to sort that Order collection when your eagerly fetching the Orders. In that case you can use AssociateWith Method and specify the column that you want to order your Orders Collection by. Here is an example that accomplishes that.
In the above code, I am using LoadWith method which basically says that when you are retrieving the customer also retrieve its Orders as well. You can see from the output on the screen, when I traverse the Orders Collection, there is no additional query being sent to the database. Initial query retrieves both Customer and its orders. Further down I am making use of AssociateWith which sorts the orders by lowest freight. You an confirm from the output that we are printing all the Orders for ALFKI customer in ascending order of freight.
If you have defined foreign key constraints on your tables in the database, LINQ TO SQL would generate Association attribute on both sides of the entity meaning the parent and child entities. Let's look at the association attribute generated by Linq To SQL on both side of the entities for Customer and its Order Collection.
Customer Entity
Order Entity
In the Customer entity we have Orders property that allow us to navigate to its related Orders. Customer and Orders are associated in the database by foreign key relationship. Linq to SQL uses association attribute to map the foreign key relationship into parent child hierarchy. The name attribute represents foreign key constraint name, storage specifies the private level variable that would store the collection of orders for the customer. The OtherKey argument is the most confusing to most people. OtherKey basically represents the name of the member on the related Order entity.
In the association attribute on Order entity, we are traversing from child to parent so we are setting IsForeignKey to true which basically states that we are on the child side of the relationship. Since we are on the child side of the parent child relationship we are also specifying the column in the Order entity that relates the Order to a Customer. To represent the relationship, we make use of ThisKey attribute which represents the CustomerID property on the Order entity.
When you are programming in .net, you can apply inheritance and polymorphism concepts on your entity classes to solve your business problems. However when you are saving those objects to the database, there is no clean way to store different child entities having some common properties that they inherit from the base class and some properties that are specific to that child entity. One of the ways to store these objects in the database is by adding all the possible fields available for all the child and base classes and marking them as allow null. Then you simply set appropriate values for only the fields that are applicable to that specific child classes. In order to differentiate each row and identity what child class it is, you can make use of discriminator column. Let's walk through an example to get a clear understanding of how to structure your inheritance hierarchy in the database.
In the above code, I have a table called worker which contains different type of Worker. All workers basically inherit from a base class called Worker. The base class for the worker has one property called Name which every derived class inherits. Every derived class has some properties that are specific to that class itself. For Example the first record with Alvin is Salaried Worker which has a property of FixedSalary. Keven worker is an Hourly Worker which uses Hourly Rate and Hours column. Jack is a commission worker that uses commissionperc and WidgetSold column to get its salary. Tony is a SalariedBonus Worker which uses Fixed Salary column and a bonus column to determine its salary.
Now that you have seen how these rows are defined in the table, the question becomes how do we map these different type of records to different entities and map their specific columns to properties on the derived classes. Linq to SQL allows child classes derived from the same base class to map to the same table in the database. You basically use Inheritance attribute on your base class to define different derived entities having different value for the discriminator column. The Code parameter specifies the specific value for that discriminator column that would map that record to the entity specified in the Type attribute. Below is an example of how I am mapping my table to entities defined using Linq to SQL attributes to map my records to different derived entities.
In order to set up my inheritance on my derived classes, I first define Type property on my worker which determines what type of object to instantiate for a particular record in the database. Next I specify all the inheritance mappings to all the derived classes based on the a specific valued found in the discriminator column by making use of Code attribute. Since my worker class is defined as abstract, Linq to SQL cannot instantiate Worker class. Hence I have to define the default mapping to use in the case if the record in the database defines no value for Type column. In my case I have specified to create an instance of SalariedWorker when the type attribute is missing in the database. Also notice that each derived classes have their own specific properties are related to that derived class. I am also providing an abstract method called GetSalary. Each derived entity overrides the GetSalary method, and using its properties calculates the appropriate salary based on its type.
Once I have configured my classes, we will see how to query for our derived classes using Linq syntax and how to insert derived entities in the Worker table. Below is the code that shows how to accomplish that.
In the first query I am getting a collection of workers and printing their name,type and salary. Because of my inheritance mapping my workers collection consists of different derived entities. However I use the base class reference to get the salary of each worker. Since every derived class overrides the method, they get to specify how to get the salary for their type using polymorphism.
In the second query I am simply getting the first worker who is salaried worker. In the background Linq to SQL generates query against worker table with type filter of Salaried Worker. The third query uses is operator to only retrieve the worker of type CommissionWorker. The query is not any different from our previous query. Only difference is, when you use OfType operator you get a strongly typed derived class where as when you use is operator you get the derived object but its uses base class reference. Therefore in my code when I had to access specific property on the Commission Worker, I had to the cast Worker object to commission worker to access properties specific to commission worker.
In the last portion of my code, I create an an array of two different types of derived entities and specify their respective property values. I am than persisting those objects inside the database. Output below confirms that our record got inserted in the database along with its type discriminator column.
Most of us don't have to worry about how linq does all the magic in the background of persisting our objects and syncing up our primary keys that are auto generated in the database. However if you are manually creating your class it is good to know what attributes are must and required to be able to save objects to the database and how to sync the auto generated id created by the database with property defined as Primary column in the entity class. If you want to sync up primary key identity column with entity member when you insert an instance of the entity class, you have to set the column defined as Primary Key column and set the column as IsDbGenerated to true. Here is a very simple example of a datacontext and Categories class that has IsDbGenerated set to true for CategoryID column.
In the above code, I have marked my CategoryID column as primary key. This is required by linq to SQL to insert records into the database. You must have at least one column as primary column. Secondly I am also setting IsDbGenerated to true to sync up the categoryId column with the auto generated id.
For every column in the database, linq to SQL generates a property that maps a particular column in the database to the property defined on the entity class. However if you look at the setter property generated by the designer, it has all the details which raises events that notifies to the subscribers that property has changed. Basically for a property called ProductName, the designer generated code will raise a before and after event. Further more the setter also notifies the tracking service that property has been changed by the end user code. Due to this reason, linq to SQL is able to generate the appropriate SQL statement to send the changes to the database. However, when we fetch an entity from the database, linq to SQL has to go through the same process of assigning the property value by reading the column from the database, which is totally not required. We do not want linq to assign our property using the setter because the values assigned would be tracked and there is no point in tracking the value since it was not changed by the end user code. This is where we can use the attribute called Storage which specified the private level variable that linq to SQL can directly use to by pass property accessors and interacts directly with our field level variable. By default the designer ensures this for your by making use of Storage Attribute. However there is nothing stopping you from changing this behavior if you want linq to SQL to use property accessors and go through your business logic defined inside your setter. Here is a an example of the code generated by the linq to SQL designer.
Table valued function are nice way to apply further transformations to your linq queries. The reason is, they return IQueryable instead of stored procedure returning IEnumerable. I will not delve into the original problem that I was facing at work, but try to come with an example that identifies the issue I was facing with linq to SQL.
I have a table valued function that simply returns two categories, Beverages and Produce. While I am loading the categories, I also want to eager load Products for those categories as well. On retrieving the categories I simply print those categories to the output screen. Here is an example.
In the above example, I have a table valued function called BvgAndSeaFood that simply returns two categories called Beverages and Produce. In the order to use the table valued function, I go to the linq to SQL designer and drag the function onto the categories table which basically means that the table valued function returns strongly typed instance of categories. In the query, I am making use of dataloadoptions to turn eager loading. I am saying that while you are loading categories, also load the products for those categories as well. Once I have configured my dataloadoptions I assign the object to the datacontext. Than I am printing the categories to the output screen. Seeing from the results, you would realize that data returned on the screen is incorrect. The results should have been Beverages and Produce not Beverages both times. I simply couldn't figure out why the results that I was getting was incorrect. If you turn off eager loading you get correct results. Here is the changed query.
Once I removed the eager loading options, the query results returned were correct. This leads me to say that there is something that does not work correctly when you are returning results from a table valued function when eager loading is turned on. After couple of hours of playing around I finally found a work around. Essentially, the categories that I got from the table valued function, I joined them back to categories table and for some reason that fixed the duplicate categories. Here is the changed query.
In order to fix the problem I encountered, I simply join the results from the table valued function with categories collection on the datacontext and returning categories from the categories table. I consider this a work around, but I really want to know why my original query failed with eager loading turned on. Perhaps readers can shed some light on this matter.
More Posts
Next page »