Archives
-
Synchronizing AutoGenerated Properties After SubmitChanges
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.
-
ExecuteQuery to execute dynamic SQL in Linq To SQL
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.
-
Executing Aggregates on SQL Server using Linq To SQL
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.
-
Change Notification In Linq To SQL
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.
-
Sorting eager loaded Child Collections With Linq To SQL
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.
-
Understanding Association Attribute
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.
-
Inheritance in Linq To SQL
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.
-
Retrieving AutoGenerated Id from the database
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.
-
Using Storage Attribute to bypass property accessor
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 and Eager Loading Does not work
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.
-
Join with Into Clause
Joins in linq to SQL are not any different than SQL server. Join basically joins the results of two or more table and shows only record that match in the outer sequence that has rows present in the inner sequence. Here is an example that illustrates that.
-
Defaults In Linq
There are several query operators available in Linq that supports the concept of Default like SingleOrDefault or FirstOrDefault etc. Default returns the default value for that type. If the type is integer the default would be an int. If the type is string that the default would be a null. However if type is nullable ints than the default value would be nullable<int>. Here is an example of that.
-
Find Overloads of a Method
Its pretty amazing that querying concept has completely eliminated my use of loops and if statements. Today I was playing around with someone's class library which was pretty big. I decided to print out all the methods and its overloaded versions available. Here is an example of printing out the methods available in int class and the display also shows how many times a particular method is overloaded.
-
All Operator Includes Empty Collection
If you have not used All operator with LINQ query, you will realize that ALL operator will include collection that meet the criteria condition as well include collection that is empty. So if you have a query where you are trying to find customers who have all orders that are placed in city of London, than your customer results would include orders from city of London as well customers who have not place any orders at all. Let's take a look at an example.
-
Cant Do ServerSide paging With DataPager
I am pretty sure I checked all my alternatives but I cant seem to find a way to do server side paging with ListView control and DataPager. Most of my existing code base does not use object datasource or LinqDataSource control which exposed a property called SelectCountMethod that gets the value of the total rows available for that select clause. Usually for server side paging you provide the rows which the user can see which is the startrow and maximum rows and than for the DataPager to build its UI, you provide method for SelectCountMethod that retrieves the count of actual rows available. But how do you accomplish this task when you are not using objectdatasource control. Well DataPager exposes a property called TotalRowCount whose value get set by the object datasource or linq datasource control. However this property is only readonly, which you cannot set if you are manually binding the ListView Control. I am not sure why the architecture was done this way which forces the use of a specific control to achieve server side paging.
-
Yield return break;
The easiest way to end a method call is making a return statement. However when we are using iterators in .net 2.0 we can make use of yield return I to return the next element in the collection. Yield return I does not end the method but returns the next value in the collection and halts the method until the next MoveNext is called. So how do you deal with a scenario where you want to end iteration early based on some reason. Well, you can make use of yield return break which simply breaks out of the method. If you have any finally block declared, those will also get executed. Let's have a look at an example.
-
using TypeName with MasterType directive
In the past I have only used Virtual Path attribute with MasterType directive. Using the MasterType attribute gives the page a strongly typed master page instead of casting the master page to correct type and accessing the property. But what happens if you specifying the master page dynamically based on condition. In that case virtual path attribute would not work because you do not know what master page you are going to be inheriting from. The solution is to create a base master page and put your common master page properties in the base class. Now in order to get a strongly typed references to master page properties in your pages, use the MasterType directive with TypeName attribute which you can set to your custom master page class. here is an example.
-
Use Default constructors For Query continuation
If you would like to build your queries based on existing queries, it is essential that you use object initializer instead of using non default constructor. The reason is non default constructors have no direct translation in SQL server. Therefore you cannot further reuse your queries and you would have to force rest of the query to be executed in memory. Here is an example that illustrates this behavior.
-
Reusing Linq Queries
Today I was working on a class library(someone else wrote) that used Linq extensively. One of the problems I noticed was, there was no reuse of queries in the class. Every method pretty much return List<T> where T was a custom class that was created by joining three Linq to SQL tables. Here is an example of that.
-
DeleteAllOnSubmit sends individual Delete Statement
In the application that I am currently working, the user interface allow the end user to select multiple items and mark them for deletion. After marking them for deletion they have to click the submit button to send the changes to the database. Well, I had the those objects in memory so I simply passed those objects to DeleteAllOnSubmit method exposed on datacontext and called submitchanges. I thought that linq to SQL would issue a single statement to the database to delete all those objects but it issued seprate SQL statement to delete each object. Here is an example that illustrates the behavior.
-
Filtering DataSet using linq
Although DataSet provides some querying capabilities using its Select method but the usage is very limited and the result is not strongly typed. Using Linq to DataSet extensions you can apply unlimited querying capabilities and also transform your DataSet into strongly typed objects. Here is an example where I am returning products collection from a DataSet and also applying a filter on the DataSet to return products rows that belong to category of beverage.
-
Using Linq to Find Items Selected In ListBox
If you are trying to find items selected in a ListBox, you could do it the old fashioned way by looping through list items and checking to see if item is selected and if the item is selected you grab the selected value and create an instance of the object, assign the selected value to the object and add the object to the collection. However a simple query makes this entire process only 1 liner. Here is the code that I used to grab selected items from a ListBox.
-
Returning Empty Collection
If you ever wanted to return an empty collection instead of returning null, you can use Enumerable.Empty. The Enumerable.Empty creates an empty collection of correct type specified by the generic parameter. Here is an example of returning generic empty collection.
-
SiteMapPath Control is Screen Reader Friendly
I was surprised that SiteMapPath control is screen reader friendly. It exposes a property called SkipLinkText whose value defaults to "Skip Navigation Links". When screen reader starts reading the page it will ignore all the links and only read the text specified on SkipLinkText. If you want screen reader to read a different text than what its default to, simply change the text to the content that you want the screen readers to read. The text you specify for SkipLinkText property will not effect you regular html rendering.
-
MasterType directive
In the past, if I had to access specific property of a master page from my content page, I usually would cast my master page to right type and get access to property but this could lead to runtime errors. Today I discovered that you can apply master type directive on your content page to make your master page reference strongly typed. Once you apply the MasterType directive, you no longer have to cast the Master Page to correct type because it is casted to the right type already. Here is snippet that you can put in your content page.
-
Using ConfirmButtonExtender with ModalPopup Extender
Today at work, an end user came and ask me that delete button simply deletes the row from the grid and does not give me a confirmation dialog. Can you fix this? I said sure. I simply had to add button extender to my grid view and I can then display the alert popup as soon as user clicks delete button. But obviously the alert box that comes up is pretty boring and there is not much you can do to spice it up. Than after researching a little bit, I realized that I could actually extend my button control extender with modal popup control extender. Button Control Extender has an additional property called displaymodalpopupid where you can reference the id of the modal popup extender. After that I simply added a modal popup extender and specify the targetcontrolid to be the delete button and popup control id to be the panel that will displayed. Here is the markup that creates a customized confirm dialog box.
-
Returning Anonymous Types from WebService to asp.net Ajax
If your objects do not have complex types, you can have your WebService return an array of anonymous types and .net serializer will correctly serialize your objects to be easily consumable from JavaScript code. In order for you to return anonymous types from your WebService method simply return an array of objects because you do not know what the actual type is? Below is a simple example that demonstrate how to consume an array of anonymous type from asp.net client side.
-
Missing Association Links
I must have spent good one hour trying to figure out why when I do category and dot, the products collection does not appear. I confirmed in the designer that category and product has an association link but when I open up designer.cs, I don't see Products EntitySet defined in Category class. It's really confusing why the designer shows that there is an association between category and products but the code is not generated. After an hour I finally realized that my category table did not define categoryid as primary key column. So keep in mind that if your table does not have primary key column defined, linq to SQL designer would not generate the code for association relationship.
-
Creating DataBound Control
The other day at work, I was working with working with collection of data from database and wondered how hard would it be to implement a custom DataBound control. Surely enough on researching, I found out that you can inherit from CompositeDataBoundControl and override CreateChildControls to bind the data to the markup user has specified in ItemTemplate property of the control. Here is an example of a SimpleDataBoundControl class that I have created that inherits from CompositeDataBoundControl.
-
UseSubmitBehavior=false
Most of the viewers would agree that there are so many gems hidden inside of asp.net that it takes some times years coming across them. Today I discovered SubmitBehavior property on button control. By default all button uses browser's default submit behavior to post the page back to the server. However there are cases when you are building server controls where you need explicit control as well as access to the JavaScript function that causes that button to post the page back to the server. In those scenarios, you can set the SubmitBehavior = false which will cause __doPostback JavaScript function to be attached to the button that will handle the postback for the button. On the server side, if you want to access the JavaScript function that causes the postback for the button you can use GetPostBackEventReference to access the JavaScript function. Here is a simple markup that uses UseSubmitBehavior property and also shows the JavaScript function emitted by asp.net.
-
ASP Namespace
ASP is the namespace where all the generated code files in asp.net reside. When a request is made to IIS for a page, if it ends in aspx extension, the request gets sent to aspnet worker process where the aspx file is converted to a class with filename_aspx and stored in Temporary Asp.net Files. By default all these generated files are stored in ASP namespace. In fact in visual studio, you can get intellisense of on these files as shown below.