LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Over the last few weeks I've been writing a series of blog posts that cover LINQ to SQL.  LINQ to SQL is a built-in O/RM (object relational mapper) that ships in the .NET Framework 3.5 release, and which enables you to model relational databases using .NET classes.  You can use LINQ expressions to query the database with them, as well as update/insert/delete data.

Below are the first eight parts in this series:

In Part 5 of the series I introduced the new <asp:LinqDataSource> control in .NET 3.5 and talked about how you can use it to easily bind ASP.NET UI controls to LINQ to SQL data models.  I also demonstrated how to use it a little more in a follow-up post I did that discusses the new <asp:ListView> control (Part 1 - Building a Product Listing Page with Clean CSS UI).

In both of these articles the queries I performed were relatively straight-forward (the where clause worked against a single table of data).  In today's blog post I'll demonstrate how to use the full query expressiveness of LINQ with the LinqDataSource control, and show how you can use any LINQ to SQL query expression with it.

Quick Recap: <asp:LinqDataSource> with a Declarative Where Statement

In these two posts I demonstrated how you can use the built-in filter capabilities of the LinqDataSource control to declaratively express a filter statement on a LINQ to SQL data model. 

For example, assuming we had created a LINQ to SQL data model for the Northwind database (which I covered how to-do in Part 2 of this series), we could declare a <asp:LinqDataSource> control on the page with a declarative <where> filter that returns back only those products in a specific category (specified via a querystring "categoryid" value):

 We could then point a <asp:gridview> control at the datasource and enable paging, editing, and sorting on it:

When we run the above page we'll then have a GridView with automatic sorting, paging, and editing support against our Product data model:

Using declarative <where> parameters like above works well for many common scenarios.  But what happens if you want the Product filtering to be richer or more complex?  For example, what if we only wanted to display products made by suppliers based in a dynamic set of countries? 

Using the <asp:LinqDataSource> Selecting Event

To handle custom query scenarios you can implement an event handler to handle the "Selecting" event on the <asp:LinqDataSource> control.  Within this event handler you can write whatever code you want to retrieve a data model result.  You could do this with a LINQ to SQL query expression, or call a Stored Procedure or use a Custom SQL Expression to retrieve the LINQ to SQL data model.  Once you retrieve a sequence of data, all you need to-do is to assign it to the "Result" property on the LinqDataSourceSelectEventArgs object.  The <asp:LinqDataSource> will then use this sequence as its data to work with.

For example, below is a LINQ to SQL query expression that retrieves only products from suppliers based in a specific set of countries:

VB:

C#:

Note: you do not need to write your query expression in-line within the event handler.  A cleaner approach would be to encapsulate it within a helper method that you just call from the event handler.  I show how to create one of these helper methods in the beginning of my Part 8 blog post (using a GetProductsByCategory helper method).

Now when we run our page using the custom Selecting event handler, we'll only see those products whose suppliers are located in our array of countries:

 

One of the really cool things to notice above is that paging and sorting still work with our GridView - even though we are using a custom Selecting event to retrieve the data.  This paging and sorting logic happens in the database - which means we are only pulling back the 10 products from the database that we need to display for the current page index in the GridView (making it super efficient).

You might ask yourself - how is it possible that we get efficient paging and sorting support even when using a custom selecting event?  The reason is because LINQ uses a deferred execution model - which means that the query doesn't actually execute until you try and iterate over the results.  One of the benefits of this deferred execution model is that it enables you to nicely compose queries out of other queries, and effectively "add-on" behavior to them.  You can learn more about this in my LINQ to SQL Part 3 blog post.

In our "Selecting" event handler above we are declaring a custom LINQ query we want to execute and are then assigning it to the "e.Result" property.  We haven't actually executed it yet though (since we didn't try and iterate through the results or call ToArray() or ToList() on it).  The LINQDataSource is therefore able to automatically append on a Skip() and Take() operator to the query, as well as apply an "orderby" expression to it -- all of these values being automatically calculated from the page index and sort preference of the GridView.  Only then does the LINQDataSource execute the LINQ expression and retrieve the data.  LINQ to SQL then takes care of making sure that the sort and page logic is handled in the database - and that only the 10 product rows required are returned from it.

Notice below how we can also still use the GridView to edit and delete data, even when using a custom LinqDataSource "Selecting" event:

This editing/deleting support will work as long as our Selecting event assigns a Result query whose result sequence is of regular entity objects (for example: a sequence of type Product, Supplier, Category, Order, etc).  The LINQDataSource can then automatically handle cases where UI controls perform updates against them. 

To learn more about how updates work with LINQ to SQL, please read Part 4 of this series.  Then read Part 5 of the series to see Updates in action with the LinqDataSource.

Performing Custom Query Projections with the Selecting Event

One of the powerful features of LINQ is its ability to custom "shape" or "project" data.  You can do this in a LINQ to SQL expression to indicate that you want to retrieve only a subset of values from an entity, and/or to dynamically compute new values on the fly using custom expressions that you define.  You can learn more about how these LINQ query projection/shaping capabilities in Part 3 of this series.

For example, we could modify our "Selecting" event handler to populate a GridView to display a custom set of Product information.  In this Grid we'll want to display the ProductID, Product Name, Product UnitPrice, the Number of Orders made for this Product, and the total Revenue collected from orders placed for the Product.  We can dynamically compute these last two values using a LINQ expression like below:

VB:

C#:

Note: The Sum method used in the Revenue statement above is an example of an Extension Method.  The function it takes is an example of a Lambda expression.  The resulting type created by the LINQ query expression is an anonymous type - since its shape is inferred from the query expression.  Extension Methods, Lambda Expressions, and Anonymous Types are all new language features of VB and C# in VS 2008.

The result of our custom LINQ expression when bound to the GridView will be UI like below:

Note that paging and sorting still work above with our GridView - even though we are using a custom LINQ shape/projection for the data. 

One feature that will not work with custom shapes/projections, though, is inline editing support.  This is because we are doing a custom projection in our Selecting event, and so the LinqDataSource has no way to safely know how to update an underlying entity object.  If we want to add editing support to the GridView with a custom shaped type, we'd want to either move to using an ObjectDataSource control (where we could supply a custom Update method method to handle the updates), or have the user navigate to a new page when performing updates - and display a DetailsView or FormView control that was bound to a Product entity for editing (and not try and do inline editing with the grid).

Summary

You can easily perform common query operations against a LINQ to SQL data model using the built-in declarative filtering support of the LinqDataSource.

To enable more advanced or custom filtering expressions, you can take advantage of the LINQDataSource's Selecting event.  This will enable you to perform any logic you want to retrieve and filter LINQ to SQL data.  You can call methods to retrieve this data, use LINQ Query Expressions, call a Stored Procedures, or invoke a Custom SQL Expression to-do this. 

Hope this helps,

Scott

Published Friday, September 7, 2007 11:52 PM by ScottGu
Filed under: , , ,

Comments

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Saturday, September 8, 2007 6:08 AM by Vikram

Thats what the doctor order for. Thanks for the notes

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Saturday, September 8, 2007 9:00 AM by Max Christian

Are queries that reference more than one database supported?  (Sorry if you've mentioned it and I've missed it amongst all the good stuff.)

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Saturday, September 8, 2007 9:08 AM by PBZ

So if I have var products = ... and later I have say e.Result = products the control will have to somehow change the products expression tree and add an order by clause. I've seen many example on how to do this in one LINQ query, but how do you do that in two or more statements? For example: var products = from p in northwind.Products select p; now I'd like to "add" a where clause to the existing products, something like this: if (somecondition) product = ???+ where countries.Contains(p.Supplier.Country) ??? I put "???" because I don't know how the syntax should look like. Thanks.

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Saturday, September 8, 2007 12:11 PM by ScottGu

Hi Juan,

Thanks a bunch for the great translation - I really appreciate it!

Scott

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Saturday, September 8, 2007 12:13 PM by ScottGu

Hi Max,

>>>>>> Are queries that reference more than one database supported?  (Sorry if you've mentioned it and I've missed it amongst all the good stuff.)

You can have queries that span multiple tables, but you can't automatically join against two separate databases and have it do a distributed query against both.

What you can do, though, is perform two (or more) queries against multiple databases, and then do a local join on the data using LINQ in the middle-tier.

Hope this helps,

Scott

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Saturday, September 8, 2007 12:17 PM by ScottGu

Hi PBZ,

>>>>>>>> So if I have var products = ... and later I have say e.Result = products the control will have to somehow change the products expression tree and add an order by clause. I've seen many example on how to do this in one LINQ query, but how do you do that in two or more statements? For example: var products = from p in northwind.Products select p; now I'd like to "add" a where clause to the existing products, something like this: if (somecondition) product = ???+ where countries.Contains(p.Supplier.Country) ??? I put "???" because I don't know how the syntax should look like.

If you have a query like this:

var firstProductsQuery = from p in northwind.Products

                        select p;

You can then create an additional filter query using with an expression like below:

var secondProductsQuery = from p in firstProductsQuery

                         where p.UnitPrice < 4

                         orderby p.ProductName

                         select p;

Note with the second query how the from statement works against the first query.  At runtime LINQ to SQL will optimize things so that only one query is executed (its query processor will merge the two queries into a single SQL statement).

Hope this helps,

Scott

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Saturday, September 8, 2007 12:45 PM by Bryan Watts

Scott, this is an excellent series of articles. LINQ to SQL is a fantastic technology and I applaud the efforts of everyone involved.

I have a question about the Settings.settings file. When I modify my model in the designer, a connection string setting is automatically created for me. This setting passed to DataContext by the generated default constructor.

This introduces 2 issues with the connection string:

1. It can be acquired by reflecting DefaultSettingValueAttribute

2. It requires compilation to change

I know I'm probably missing something, but here is my scenario:

MyApp.Data resides in its own project. An App.config file was generated with a connection string named "MyApp.Data.MyAppConnectionString" and the correct value. I assumed that if I added this configuration element to Web.config, it would override the compiled setting and everything would work.

I copied the element, below, to Web.config:

<connectionStrings>

   <add

       name="MyApp.Data.MyAppConnectionString"

       connectionString="..."

       providerName="System.Data.SqlClient"

   />

</connectionStrings>

I changed the connection string slightly to see if it is using the correct setting, and ran the web app. It uses the compiled setting. For the life of me, I cannot get it to use the setting out of Web.config; it is always the setting as originally defined.

How do I override the connection string placed in Settings.settings? I realize that I can do this:

MyAppDataContext data = new MyAppDataContext(...conn string...)

But that means anyone creating MyAppDataContext would have to know to use this constructor, and all information necessary to get the connection string. I could have a common method which is responsible for reading the connection string and creating an instance, but then you would need to know about that method, which is now even further away from the default constructor.

Even worse, if someone does use the default constructor, as anyone would assume they could, an irrelevant connection string would be used.

I'd like to remove the connection string completely from compiled use, and use a configured one for the default constructor.

Is this possible? Thanks for reading!

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Saturday, September 8, 2007 2:31 PM by Ben Hayat

Hi Scott;

In your Even handling for DataSourceSelecting Event, you declare an instance of the DataConrext called NorthWind. Then you pass the query result to the eventsArgs result.

By this time, the event is ended and the datacontex is closed. How does LinqDataSource gets to use this query? Does LinqDataSource use Attach() internally to create new obeject collection of those products?

Thanks!

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Saturday, September 8, 2007 2:36 PM by ScottGu

Hi Bryan,

>>>>>> I have a question about the Settings.settings file. When I modify my model in the designer, a connection string setting is automatically created for me. This setting passed to DataContext by the generated default constructor.

By default the DataContext should look in the web.config file for the connectionstring setting, and only fall back to using the one compiled into the class if it can't find one.

Can you send me email (scottgu@microsoft.com) with more details about your project (potentially even including a .ziped up sample)?  I can then take a look to make sure the names match in the connection-string and the DataContext class.

Hope this helps,

Scott

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Saturday, September 8, 2007 2:44 PM by ScottGu

Hi Ben,

>>>>>> In your Event handling for DataSourceSelecting Event, you declare an instance of the DataConrext called NorthWind. Then you pass the query result to the eventsArgs result.  By this time, the event is ended and the datacontex is closed. How does LinqDataSource gets to use this query? Does LinqDataSource use Attach() internally to create new object collection of those products?

The query sequence I assigned to e.Result maintains a reference to the DataContext object.  So even though it goes out of scope of the event handler, it can still be used to execute the query and return back a sequence of Product objects.

When the request ends and the LinqDataSource is disposed of, the DataContext will be released entirely.

Hope this helps,

Scott

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Saturday, September 8, 2007 3:01 PM by Ben Hayat

>>So even though it goes out of scope of the event handler, it can still be used to execute the query and return back a sequence of Product objects.

When the request ends and the LinqDataSource is disposed of, the DataContext will be released entirely.<<

Thanks Scott; I'm glad I asked this question. Your answer has opened my mind to the point that as long as the DataSource has a reference to the DataContext, the DataContex sticks around. Great answer, thank you!

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Saturday, September 8, 2007 3:42 PM by logic

Scott,

Is it possible to mix VS 2005 and 2008 in one project?

That is, I have a project that is already completed using 2005. Now, is it possible to add some 2008 functionalities and controls to the project? For instance, I would like to use Listview control, etc.

Cheers,

logic

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Saturday, September 8, 2007 4:59 PM by Mark Leistner

Sorry, a little off topic, but does anyone know of a way to make EntityRef and EntitySet work with LINQ to Objects?  Could you possibly intercept the query before linq processes it and rewrite the expression tree?

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Saturday, September 8, 2007 7:10 PM by ScottGu

Hi logic,

>>>>>> That is, I have a project that is already completed using 2005. Now, is it possible to add some 2008 functionalities and controls to the project? For instance, I would like to use Listview control, etc.

You can use VS 2008 to open a VS 2005 project and add new .NET 3.5 features.  But unfortunately if you add .NET 3.5 features you won't be able to use the project still in VS 2005.

Hope this helps,

Scott

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Saturday, September 8, 2007 7:13 PM by ScottGu

Hi Mark,

>>>>>> Sorry, a little off topic, but does anyone know of a way to make EntityRef and EntitySet work with LINQ to Objects?  Could you possibly intercept the query before linq processes it and rewrite the expression tree?

If you implement IQueryabe<T> on an object, you can intercept and participate in the execution of a LINQ query.  Matt Warren has a pretty deep series that talks about how to-do this here: blogs.msdn.com/.../linq-building-an-iqueryable-provider-part-vii.aspx

Hope this helps,

Scott

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Sunday, September 9, 2007 11:32 AM by Ben Hayat

OT Question

Hi Scott;

Will there be any more articles on ListView working with Linq?

Thanks!

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Sunday, September 9, 2007 12:58 PM by Mihai Dinculescu

Using the below piece of code gives "The query results cannot be enumerated more than once.".

RealmHistoryDataContext db = new RealmHistoryDataContext();

var result = db.GetBrowseGuilds(Zone, Language, Faction, RealmType, Recruiting, BossId.ToString(), BossIdMax.ToString());

e.Result = result;

Replacing "e.Result = result;" with "e.Result = result.ToList();" fix the error, but it will also break the intelligent paging according to what you say in this article.

Also, is there a way to get the total number of rows the sproc returns, for putting it in a outside text field ?

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Sunday, September 9, 2007 7:58 PM by Clayton Powell

When using the LinqDataSource to put, say, Product rows into a GridView can you get the in-line editing behaviour when only showing some of the columns from the Product table? With previous DataSources you needed to ensure all columns where present in your table so that the parameters of the update statement matched.

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Monday, September 10, 2007 8:44 AM by ScottGu

Hi Steve,

>>>>>> These controls that query linq which is effectively writing inline sql into a UI control is a practice that I'd expect for some hobbyist website, not an enterprise level solution.

One of the things I mentioned in my post above is that you *do not* need to put the LINQ query in your actual code-behind page, and that a recommended practice would be to encapsulate it within a helper method in a separate class.  This enables you to cleanly separate your query logic from the actual presentation pages.  My previous posts in this series demonstrated how to-do this, and I'll be covering it more in the future.

Hope this helps,

Scott

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Monday, September 10, 2007 8:44 AM by ScottGu

Hi Ben,

>>>>>> Will there be any more articles on ListView working with Linq?

Yep - I'll be covering this more in the future.  So many things to blog about, so little time. :-)

Thanks,

Scott

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Monday, September 10, 2007 8:46 AM by ScottGu

Hi Mihai,

>>>>>> Replacing "e.Result = result;" with "e.Result = result.ToList();" fix the error, but it will also break the intelligent paging according to what you say in this article.

Are you iterating over the result in your GetBrowseGuilds() class?  Or do you have multiple parts of your UI where you iterate over the results?  

Calling ToList() won't necessarily break smart paging - but it depends on how you are doing the page logic.  If you can post more details about this I can help.

Thanks,

Scott

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Monday, September 10, 2007 8:49 AM by ScottGu

Hi Kai,

>>>>>> I would like the generated classes mapped into my namespaces, but the diagram only supports generating Entity classes into one namespace, right?  And splitting the diagram into several files (one for each namespace) is not a good solution since some of the shared Entity classes will be generated several time.  Will it support any of this in the future, or do you have other suggestions?

If you are using the Linq to SQL designer in VS 2008, you can configure the namespace of your DataContext and the namespace of your Entity classes separately (they can be in two separate namespaces).  You can't, however, specify a separate namespace per entity if you are using the designer.  If you want this support I'd recommend declaring the entity classes by hand - in which case you'll have full support for specifying the namespace.

Hope this helps,

Scott

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Monday, September 10, 2007 8:50 AM by ScottGu

Hi Clayton,

>>>>>> When using the LinqDataSource to put, say, Product rows into a GridView can you get the in-line editing behaviour when only showing some of the columns from the Product table? With previous DataSources you needed to ensure all columns where present in your table so that the parameters of the update statement matched.

The nice thing about the LinqDataSource is that you don't need all columns to be present in order to perform updates.  You can choose the display only the columns you need/want in the GridView (or FormView, DetailsView, ListView, or any other control you use), and the LinqDataSource will handle updates fine without any additional code.

Hope this helps,

Scott

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Monday, September 10, 2007 11:03 AM by l_tauber

In an earlier response you described that e.Result holds a reference to the DataContext, and thats how the LINQ engine can execute the query even after the method has exited. I got two questions:

1. What happens if im declaring the DataContext in a using clause, for example

using(DataContext db = new DataContext()){e.Result = db.????} will the DataContext be destroyed before the query executes?

2. Should we be using the using clause on the DataContext since its IDisposable? or should we just declare it and let the runtime dispose it?

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Monday, September 10, 2007 12:19 PM by Mihai Dinculescu

>>>>>> >>>>>> Replacing "e.Result = result;" with "e.Result = result.ToList();" fix the error, but it will also break the intelligent paging according to what you say in this article.

>>>>>> Are you iterating over the result in your GetBrowseGuilds() class?  Or do you have multiple parts of your UI where you iterate over the results?  

>>>>>> Calling ToList() won't necessarily break smart paging - but it depends on how you are doing the page logic.  If you can post more details about this I can help.

Thank you for your fast response Scott.

The GetBrowseGuilds() class is not something I coded. GetBrowseGuilds is a stored proc that I've added to the .dbml file and the GetBrowseGuilds() is the generated class to handle this SP. I have not changed a single character in it.

The code I pasted is all the code I wrote in the _Selecting function. The LinqDataSource it's used to bind a GridView, and in gridview's _RowDataBound I retrieve some values and use them to pupulate some controls in the gridview. That's all I have. Results shouldn't be iterated twice.

By debuging the page I've noticed that after "e.Result = result;" it runs the GetBrowseGuildsResult { } constructor for every row returned. The error appears right after this iteration is over.

Also Ive noticed that no matter if I use .ToList() or not, the GetBrowseGuildsResult { } is  ran for all the 1.000 records the stored proc returns.

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Monday, September 10, 2007 1:03 PM by skain

Scott,

This article is quite interesting.  I am currently working on my first LINQ production application and wasn't able to figure this out on my own.  Therefore in my application I decided not to use the LinqDataSource and instead bind my LINQ queries to my controls by hand.  This of course kills my ability to have automatic efficient paging and sorting.

After reading this article I decided to try and take a shot at reimplementing with LinqDataSource but I'm having a problem that perhaps you can help with.  My app is an ASP.NET application with a lot of AJAX.  An representative page in my app takes a user through a number of data entry and selection steps all on the same page, similar to a wizard.  Pretty much each step of the process is encapsulated in its own user control.

My problem is that I'm not sure the best way to make each of these user controls aware of the place in the wizard that the user is currently at or the state of the user's previous selections/entries which are used to populate the controls that come later.

Hmm.  That's  a pretty confusing sentence I think so let me give a simple example.  Let's say my app is an order taking application to be used by customer service reps taking orders over the phone.  When a customer calls in they give the rep their personal info so the rep can look up their customer record.  This lookup functionality is contained in its own user control.  Once the proper customer record is selected the app then displays a list of previous orders for that customer.

In my current app (without LinqDataSource) I've got a CustomerSelected event on the customer search control that fires and has a reference to the customer's ID in its EventArgs.  My page subscribes to that event and when it fires it calls LoadOrdersForCustomer(int custID) on the order history control.

What would be the proper way to handle this scenario if I used a LinqDataSource on the order history control?  I don't want the Selecting event to do anything until I know what the custID is.  I was thinking perhaps I could try setting a CustID member variable on the order history control when the CustomerSelected event fires and then checking to see if that's set as the first step of my Selecting handler.  But this seems kind of risky as I'm relying on the page execution to occur in a very specific order for everything to line up.  For instance, I don't really see a way to guarantee that the Selecting event will be raised after the CustomerSelected event on the crucial partial postback right after the user selects the customer record.  If CustomerSelected were to fire after Selecting then the order history control would not populate until the next postback (assuming I preserve the state of the result of the CustomerSelected event.)

Sorry for the long post.  I hope I've been able to make my question clear.  Overall I'm really quite impressed with LINQ and think you guys have really come up with something that is both new and quite useful in the coding paradigm.  I also really appreciate these columns as it makes everything much easier to grasp.

Thanks.

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Monday, September 10, 2007 1:15 PM by Ben Hayat

Hi Scott;

>>Yep - I'll be covering this more in the future.  So many things to blog about, so little time. :-)

Scott, we are very greatful for your blogs and I hope I didn't sound "Demanding" with my question.

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Monday, September 10, 2007 2:04 PM by wayde

Thanks again for another great article Scott.

Are we going to see a piece of using Linq To Sql in a truly mult-tiered environment?  One in which clients do not have access to the DataContext and use a service tier of some sort to actually do CRUD on disconnected entities?

I'm not sure where other architects stand on this ... but I'm not comfortable with giving my clients all out access to the DataContext object so as to query anything and everything at will.

Thanks - wayde

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Monday, September 10, 2007 4:51 PM by D. Figuerres

Ahhh.....

if you use a using it will blowup with a problem accessing a disposed data object.

using( mydata db = new mydata())

{

e.Results = [whatever];

}

the method will run ok but then the page will toss an exception.

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Tuesday, September 11, 2007 7:11 PM by Steve

Scott, This is a great series of articles, thanks.  Please keep them comming.  I would like to make a request though.  I am really keen to see how Linq to sql, linq to xml would work over the internet eg. in a smart client type app.  This is where I see us heading once Silverlight 1.1 final is delivered.  However I am really keen to see how the Linq component would work.  Is there any chance that one of your future demos could be linq to a smart client or xbap application ?  Showing us how we get linq over the wire.  

Thanks again for the articles.  

Steve

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Tuesday, September 11, 2007 8:29 PM by ScottGu

Apologies for the delays in getting back to you on some of these questions.  I'm at a conference in London and am really behind on email/blog comments.  Hopefully I'll have some more time in the next 24 hours to catch up and respond to the questions above.

Thx!

Scott

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Wednesday, September 12, 2007 9:26 AM by akaTheRealMcCoy

Scott, im try to configure a Where string in the *.ascx (for example). Im use a Control parameter. Here a code:

<asp:TextBox ID="txtBox" runat="server"></asp:TextBox>

<asp:Button runat="server" />

<asp:LinqDataSource

       ID="LinqDataSourceTest"

       ContextTypeName="WebApplication1.DataClasses1DataContext"

       TableName="TestTable"

       Where="testField.Contains(@par)"

       runat="server">

   <WhereParameters>

       <asp:ControlParameter ControlID="txtBox" PropertyName="Text" Name="par" Type="string" />

   </WhereParameters>

   </asp:LinqDataSource>

Also there is a asp:GridView..

But "Contains" not working in this configuration! If im use it in the codebehind - it`s all OK!

Please, help me. Any ideas?

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Wednesday, September 12, 2007 2:46 PM by Martin Robins

I think I may have found a bug in the LINQ/SQL code generator (one that certainly makes using the resulting objects more difficult anyway).

For each of the entity classes generated, there is a static PropertyChangingEventArgs object created which is then used in *all* of the calls to the PropertyChangingEventHandler (via the SendPropertyChanging method which accepts no parameters - such as the property name).

Surely, the code generated should support the event properly and thus enable the developer to see a property that is changing before it changes. I for one would like to be able to trap this event so that I can store the original value prior to the change and thus enable undo functionality from within a base class, but with the event being fired without any valid arguments it is practically useless.

Is this likely to change for RTM? Is it an oversight? Have I simply gotten the wrong end of the stick?

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Monday, September 17, 2007 9:09 AM by johnzered

First I would like to thank you for a lovely set of articles!

My question is: is it possible to override the data source property of data classes created by OR/M? The reason to this is that I have two databases in my OR/M so when I added tables at design time I specified in the datasource property the following: databasename.dbo.tablename. The problem is that the databasename may change and then I need a simple way to change it through web.config or similar so I don't have to change it in the designer and recompile.

//johzered

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Tuesday, September 18, 2007 4:54 AM by Pete

Hey Scott,

Thanks for the wonderful articles. Please keep them coming!

Re. disposing of the DataContext.

What you've said didn't make sense to me. First of all, in your example, you don't call Dispose on the LinqDataSource. So it couldn't deterministically call Dispose on the DataContext.

Secondly, even if you do call Dispose on a LinqDataSource (e.g. in Page.Dispose), it doesn't call Dispose on the associated context. (I've only checked this by overriding Dispose in my own DataContext and setting a breakpoint; not hit).

I assumed from your articles that there's no need to call Dispose on the DataContext. I'd have thought that when you execute a query using a DataContext, it manages the db connection, opening and closing it within the materialization method call.

But, then why is DataContext disposable?

Could you clarify?

Thanks,

Pete.

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Tuesday, September 18, 2007 7:15 AM by Manjunath

Dear scott,

With LINQ CTP we had method ".Including(", it has been removed with the latest release. How do we get the similar feature in latest release. Thanks.

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Monday, September 24, 2007 12:51 AM by C. Yakimov

How paging works with *Million* of records ?

Is hard to find samples of that. I need to display 100 records per page, from a table with 20 million rows. With asp.net

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Tuesday, September 25, 2007 1:32 PM by Allan McCulloch

Hello,

Thanks for your articles so far - they have been a lot of help.

The main problem for us however is how to fit this into a multi-tier architecture. We do not want to have database queries in our presentation layer.

Many thanks,

Allan

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Wednesday, September 26, 2007 4:07 PM by Mats G Friberg

Scott

I like to print interesteing blogposts and read them on the subway back home. But when i print this i only get the first page so i guess the CSS is not working (Firefox here)

Could you make this series into PDF-files or adjust the CSS for printing?

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Monday, October 1, 2007 10:10 PM by Cine

Will RTM have support for multi-langual code-bases? E.g. The current orderby operator works vastly different on Lists, Arrays and Database. It is so bad that this operator for SQL is mostly useless unless you are using 1 (spoken) language. It totally ignores the current locale setting, and it only uses the locale that the Column in the database was created with, without any kind of warning your orderby is sorting on another locale than the set one, and no workaround exists that allows you to use paging on a sorted input (the workaround for non-paging is ofcourse convert to a list or array and then sort, but do notice you will get different results depending on which you choose).

And to illustrate the problem,

Make some table on your database called sortme and put in a column called strngvn nvarchar(100) collate Vietnamese_CI_AS and use the linq mapper...

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Globalization;

namespace ConsoleApplication1

{

public static class xxx

{

public static bool MemberWiseCompare<T>(this IEnumerable<T> source, IEnumerable<T> compareTo)

{

if (source.Count() != compareTo.Count())

return false;

List<T> l1 = source.ToList();

List<T> l2 = compareTo.ToList();

for (int i = 0; i < l1.Count; i++)

if (!l1[i].Equals(l2[i]))

return false;

return true;

}

}

class Program

{

private static readonly string[] danishwords = new string[] {

"abenraa", "aaaabenraa", "aabenraa",

};

static void Main(string[] args)

{

DataClasses1DataContext db;

db = new DataClasses1DataContext();

var allentries = from c in db.sortmes select c;

foreach (sortme so in allentries)

db.sortmes.Remove(so);

db.SubmitChanges();

foreach (string c in danishwords)

{

sortme so = new sortme();

so.strngvn = c;

db.sortmes.Add(so);

}

db.SubmitChanges();

db.Dispose();

System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("da-DK");

System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo("da-DK");

DataClasses1DataContext db2 = new DataClasses1DataContext();

var dbsorted_dk = from c in db2.sortmes orderby c.strngvn select c.strngvn;

var arraySorted_dk = from c in danishwords orderby c select c;

List<string> listSorted_dk = danishwords.ToList();

listSorted_dk.Sort();

System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo("en-US");

DataClasses1DataContext db3 = new DataClasses1DataContext();

var dbsorted_en = from c in db3.sortmes orderby c.strngvn select c.strngvn;

int ignore2 = dbsorted_en.Count();

var arraySorted_en = from c in danishwords orderby c select c;

List<string> listSorted_en = danishwords.ToList();

listSorted_en.Sort();

if (!listSorted_en.MemberWiseCompare(arraySorted_en))

throw new ExecutionEngineException("Sorting not correct");

if (!listSorted_en.MemberWiseCompare(dbsorted_en))

throw new ExecutionEngineException("Sorting not correct");

if (!arraySorted_dk.MemberWiseCompare(dbsorted_dk))

throw new ExecutionEngineException("Sorting not correct");

if (arraySorted_en.MemberWiseCompare(dbsorted_en))

throw new ExecutionEngineException("Sorting not correct");

if (!listSorted_dk.MemberWiseCompare(arraySorted_dk))

throw new ExecutionEngineException("Sorting not correct");

db2.Dispose();

db3.Dispose();

}

}

}

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Wednesday, October 24, 2007 11:17 AM by Gabriel66

Hi Scott,

I know, you are very busy, but can we hope, that You will continue this serie especially the using the LINQ to SQL in disconnected enviroment and in three tier architecture?

Thanks in advance

Gabriel

# re: LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)

Thursday, November 1, 2007 2:13 PM by Ken Plunkett

Afternoon Scott - hoping you might be able to answer few questions ...

What about performance issue/impacts with LINQ’s? One of the big issues .NET has with the dataset/datagrid handling large dataset, especially with pagination.

To get great performance MS recommends that stored procedures be written to control pagination – only let the stored procedure return the number of rows display; do not let .NET do the pagination because of pipe/packet consideration, as well as potential GC impact.

Hence, the $100K question is - how does LINQ handle this? Say you have a dataset with 1000 rows of data and you only display 50 at time – where does LINQ’s "process" the data and how does it handle pagination? Is it all .NET? How does it overcome the dataset/datagrid issue? Or is LINQ not targeted at hardcore/high performance implementations?

Thanks in advance

Ken

kenneth_plunkett@hotmail.com