Best practices re: LINQ To SQL for data access

Question

Part of the web application I'm working on is an area displaying messages from management to 1...n users. I have a DataAccess project that contains the LINQ to SQL classes, and a website project that is the UI. My database looks like this:

User -> MessageDetail <- Message <- MessageCategory

MessageDetail is a join table that also contains an IsRead flag.

The list of messages is grouped by category. I have two nested ListView controls on the page -- One outputs the group name, while a second one nested inside that is bound to MessageDetails and outputs the messages themselves. In the code-behind for the page listing the messages I have the following code:

protected void MessageListDataSource_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
    var db = new DataContext();

    // parse the input strings from the web form
    int categoryIDFilter;
    DateTime dateFilter;
    string catFilterString = MessagesCategoryFilter.SelectedValue;
    string dateFilterString = MessagesDateFilter.SelectedValue;
    // TryParse will return default values if parsing is unsuccessful (i.e. if "all" is selected"):
    // DateTime.MinValue for dates, 0 for int
    DateTime.TryParse(dateFilterString, out dateFilter);
    Int32.TryParse(catFilterString, out categoryIDFilter);
    bool showRead = MessagesReadFilter.Checked;

    var messages =
        from detail in db.MessageDetails
        where detail.UserID == (int)Session["UserID"]
        where detail.Message.IsPublished
        where detail.Message.MessageCategoryID == categoryIDFilter || (categoryIDFilter == 0)
        where dateFilter == detail.Message.PublishDate.Value.Date || (dateFilter == DateTime.MinValue)
        // is unread, showRead filter is on, or message was marked read today
        where detail.IsRead == false || showRead || detail.ReadDate.Value.Date == DateTime.Today
        orderby detail.Message.PublishDate descending
        group detail by detail.Message.MessageCategory into categories
        orderby categories.Key.Name
        select new
        {
            MessageCategory = categories.Key,
            MessageDetails = categories.Select(d => d)
        };

    e.Result = messages;
}

This code works, but sticking a huge LINQ statement like this in the code-behind for a LinqDataSource control just doesn't sit right with me.

It seems like I'm still coding queries into the user interface, only now it's LINQ instead of SQL. However, I feel that building another layer between the L2S classes and the UI would cut back on some of the flexibility of LINQ. Isn't the whole point to reduce the amount of code you write to fetch data?

Is there some possible middle ground I'm not seeing, or am I just misunderstanding the way LINQ to SQL is supposed to be used? Advice would be greatly appreciated.

Answer 1

Regardless of LINQ, I think that mixing presentation code with database-relaed code is not a good idea. I would create a simple DB abstraction layer on top of LINQ queries. In my opinion LINQ is just a convenient tool, that doesn't have a serious impact on traditional application design.

Answer 2

All your LINQ querys should be in a business logic class, no change from older methodologies like ADO.

If you are a purist you should always return List(of T) from your methods in the business class, in fact, the datacontext should only be visible to the business classes. Then you can manipulate the list in the user interface.

If you are a pragmatist, you can return a IQueryable object and make some manipulations in the user interface.

Answer 3

Here is a link that provides good information about LINQ to SQL best practices

http://www.a2zmenu.com/LINQ/LINQ%20to%20SQL%20Best%20Practice.aspx

Answer 4

Boby exactly lays the point down,

From a 3t perspective one should not conduct mapping in a DAL tier then use that logic in the UI. I still dont see how to use linq in complex projects without making an additional mapping to a datatable. I dont see any information about how to do this all i see are ways of working that are in conflict with architectural design principles.

Linq is very handy and works nice but i still dont see a way to implement it in a complex project / 3 tier enviorment with benifits over lets say duwamish + datasets.