Last week I introduced a new screencast on another OData Developer tool. It was about how to use LinqPad to query OData service with C#, LINQ & WCF Data Services Client APIs.

The screencast shows how to use LinqPad to connect to and query OData Services using LINQ and WCF Data Services Client APIs. Showing how LinqPad can be useful as a developer tool to test queries against any OData Service.

I case you missed it, you can watch it on ODataPrimer website. You can also download an HD video (~48Mbs). The package contains 2 videos. One of them is the one hosted and it’s a 10 min video. And the other is ~14 min. I had to trim it to fit into youtube. The 14 min video contains additional stuff in case you are interested.

You can also subscribe to ODataPrimer channel on yourtube for future updates and videos.

Hope you’ll enjoy it.

I had few previous posts about DDD & TDD with Entity Framework that you might need to return to them before you proceed with this post. To summarize these posts, I was trying to build testable Models that is based on EF. But EF doesn't support Persistence Ignorance in its first version which make testing it in most cases require a database connection where you can test you code against it. My target was to build a testable models based on EF and that is independent of any underlying data source.

Introduction, Domain Model Review:

More...

I decided to provide some integration testing before proceeding as a proof of concept. In integration testing I will connect to the database and perform unit testing while connecting to the database.\

This unit test is almost identical to the one in my previous post. Except here I am testing the results that is coming out from EF and Database.

My target is to test IDataContext Implemented methods in NorthwindDataContext. Find below the Integration unit testing class. More...

In return to my previous posts about DDD & TDD with EF. I discovered few catches in my design that I wished to high light and discuss in this post. To summarize my take here are the head lines:

• What is the Responsibility of ObjectContext in EF?
• What is the Responsibilities of Repositories and whether they should be coupled with EF or not?

I come up with this post after few discussions through e-mail with Davy Landman.

More...

In previous post I talked about building domain model for Northwind using Entity Framework. I used the generated ObjectContext class which is NorthwindDataContext as the basic Data Access Layer Helper. But my NorthwindDataContext is implementing custom interface I created in order to be able to make my Data Access Layer testable as well as to be independent from EF.

My target is to achieve decoupling between EF and all layers above it. This include Repository classes. This might not be of a big benefit to everyone, because my repositories implementation will still dependent on IDataContext. But for me it will help to do TDD more smoothly regardless of my underlying data access layer. Beside it might allow me in future to be able to switch from EF to LINQ to SQL and just implement my IDataContext interface for LINQ to SQL.

Source code is attached to this post, feel free to download it and explore the code

More...

Me and Amr Elsehemy are working together in something and he requested a feature in some APIs I built. In fact he liked the Rob Conery's PagedList that had been modified by Troy Goode. He used it on his jBlogMVC post series about building Blog Engine using MVC.

That was pretty simple and I noticed something missing on the PagedList that I wished to add! What about sorting. I mean build a PagedList that is sorted. So I made slight changes to the enhanced PagedList made by Troy to support sorting.

Prerequisites:

It is required that you refer to Rob Conery's PagedList and the modified version by Troy Goode before you proceed in order to understand how this PagedList is useful and helpful with MVC and LINQ to SQL as well as LINQ to Entities.

Summary of changes:

To summarize the changes I made, simply I used Lambda Expressions to enable sorting. And had to modify Class declaration (with constructors) and the Initialize method. Sorting is provided by OrderBy extension method. Please return to method documentation for more details.

More...

In my previous post I talked about what is not supported in LINQ to Entities and made a simple comparison with LINQ to SQL show that these unsupported things works fine in LINQ to SQL.

Here I am going to provide workarounds these things. First one will be wrapping the entities with Business Classes. And the second way will be using client evaluation which is converting to work with LINQ to Object. Before you go further, I recommend that you read my previous post to save your time.

More...

Recently and after .Net 3.5 SP1 & Visual Studio.Net 2008 SP1 released, I started to play with LINQ to Entities. Really Entity Framework is interesting. Maybe not the best ORM tool, but still it is interesting and easy to use plus it is all one IDE at the end.

At the same time I was watching screen-casts made by Rob Conery about Building MVC Storefront along with TDD. In fact he was using LINQ to SQL. And I liked his coding techniques presented in his sample application and screen-casts.

I decided to play around with LINQ to Entities with MVC. Here I am going to talk about my adventures with LINQ to Entities, and really it is completely different from working with LINQ to SQL.

My recommendation for every developer is to start by reading Supported and Unsupported Methods (LINQ to Entities). Yes it will save you sometime. Do You know LINQ to SQL? Then you are familiar with LINQ. But you need to know what there and what is not there in LINQ to Entities. This even Might help you to build a wish list for LINQ to Entities also ;O).

Here I'm going to use some code samples presented in MVC Storefront and show some of the techniques that will not work with LINQ to Entities. And I will compare code with LINQ to SQL. In some other post, I'm going to present some other workarounds provided by ADO.NET Team when I asked them about the issues I have with LINQ to Entities. Also I'll present my own workarounds. And I'll compare the Generate SQL statements. I am not query optimizer, so I will not say what is the best choice. But you can help me in that.
More...

Yesterday, I was reading Efficient Paging in SQL Server 2005 post by Justin Etheredge, then in the comments I found Kevin Hazzard pointing to his Efficient LINQ to SQL custom paging approach. I liked his way, which reflects Justin's method which I already seen before but never apply it. I think it is more efficient than my method I provided in an earlier post. And if you googled about custom paging with LINQ to SQL you'll find few more implementation.

Today, I'm updating my method and provide the same sample I made last month with Kevin's method. I made an extension method to IQueryable Interface and called it KavinPage. You can view a demo here

Here is the code for the Extension Method:

   1: public static IQueryable<T> KevinPage<T, TResult>(this IQueryable<T> obj, int page, int pageSize, System.Linq.Expressions.Expression<Func<T, TResult>> keySelector, bool asc, out int rowsCount)

   2: {

   3:     rowsCount = obj.Count();

   4:     if (asc)

   5:     {

   6:         return obj.OrderBy(keySelector).Skip(page * pageSize).Take(pageSize);

   7:     }

   8:     else

   9:     {

  10:         return obj.OrderByDescending(keySelector).Skip(page * pageSize).Take(pageSize);

  11:     }        

  12: }

It is all about Extension Methods and how to use them to build SQL Queries. You can return for Kevin's post for detailed explanation. Also you can return to my previous post about this subject for more details about how to put it all together to build Paging ASP.NET ListView using LINQ to SQL and ObjectDataSource.

Download the updated sample.

Last week I posted about building custom paging with LINQ to SQL. And I wrapped the functionality with Extension Method to IQueryable<T> Interface.

Today I'm going to put the custom paging in a practical sample using ASP.NET ListView, DataPager and ObjectDataSource Control. Click here to download the sample. View demo here.

The sample is using Northwind Database. So first I created a Northwind LINQ to SQL class ".dbml"

More...

Introduction:
Since my early days with web application and I used to observe people writing different techniques for paging data coming from the database. Many articles where written, and different techniques for different databases. All that I am talking about paging data on the database before returning results to the application which will renders it to the client.

One Technique:
You might be familiar with queries like this one:

   1: SELECT TOP (10) [R1].[ProductID], [R1].[ProductName]

   2: FROM (

   3:     SELECT TOP (77) [R0].[ProductID], [R0].[ProductName]

   4:     FROM [dbo].[Products] AS [R0]

   5:     ORDER BY [R0].[ProductID] DESC

   6:     ) AS [R1]

   7: ORDER BY [R1].[ProductID]

The above query is one of the techniques used for paging data on the database side. When wrapping this query with a stored procedure it will look like this:

   1: CREATE PROCEDURE [dbo].[usp_Product_GETPAGE]

   2: (

   3:     @page int,

   4:     @pagelength int,

   5:     @sortfield varchar(100),

   6:     @descending bit,

   7:     @rowcount int output

   8: )

   9: AS

  10: BEGIN

  11:     SET NOCOUNT OFF

  12:     DECLARE @Err int    

  13:     SELECT @rowcount = COUNT(*) from [Products]    

  14:     declare @innerrows int

  15:     declare @sortdesc varchar(100)

  16:     declare @sortasc varchar(100)

  17:     declare @a varchar(6)

  18:     declare @b varchar(6)

  19:     IF @descending=0

  20:             BEGIN

  21:                 set @a = ' DESC '

  22:                 set @b = ' ASC '

  23:             END

  24:     ELSE

  25:             BEGIN

  26:                 set @a = ' ASC '

  27:                 set @b = ' DESC '

  28:             END    

  29:     IF charindex(@sortfield, ' [ProductID]') > 0

  30:         BEGIN

  31:             set @sortdesc = ''

  32:             set @sortasc = ''            

  33:         END

  34:     ELSE

  35:         BEGIN

  36:             set @sortdesc = ', [ProductID] ' + @a

  37:             set @sortasc = ', [ProductID] ' + @b

  38:         END   

  39:     set @innerrows = @rowcount - (@page * @pagelength)

  40:     DECLARE @sql nvarchar(1000)

  41:     SET @sql = 'SELECT TOP ' + STR(@pagelength) + ' [ProductID], [ProductName] FROM

  42:             (

  43:                 SELECT TOP ' + STR(@innerrows) + ' [ProductID],

  44:                 [ProductName]

  45:                 FROM

  46:                 [Products] 

  47:                 ORDER BY [Products].' + @sortfield + @a + @sortdesc + ' 

  48:             ) Alias

  49:             ORDER BY Alias.' + @sortfield + @b + @sortasc    

  50:     EXEC (@sql);

  51: END

The above procedure is somehow complex because it support paging and that is why it build the SQL statement and use EXEC function.

I'll not discuss its performance; my target is to discuss how to convert this query into LINQ query using extension methods then generalize it to be as an Extension Method for IQueryable Interface.

LINQ Equivalent:
The LINQ Equivalent is very simple using Extension Methods of the IQueryable and IOrderedQueryable interfaces. The following code shows that:More...