linq

LINQ Group by MAX Date Query

Matt Simner C#, LINQ ,

I’ve found some weird, wonderful and ridiculously complicated LINQ queries for getting the row with MAX(DATE) based on a key.  Most unnecessarily use lambda expressions, and some just had several interim steps.  I knew there had to be a better way, and found an unassuming post at the bottom of a StackOverflow page.

Here’s my non-lambda’d, contrived example…  assuming you’ve got an EntityFramework model (i.e. context)

            //Get Client Order with (max) order date 
            var maxclientOrder = (from clientOrder in context.ClientOrders
                               where clientOrder.OrderDate ==
                               (from clientOrder2 in context.ClientOrders
                                where clientOrder2.ClientID == clientOrder.ClientID
                                    select clientOrder2.OrderDate).Max()
                                select clientOrder).ToList();

LINQ to SQL Connection Strings with Class Library and Web.Config

Matt Simner .NET Framework, C#, LINQ , ,

Most Microsoft technologies that you can operate with a GUI come with some tradeoffs.  Things have certainly improved over the years and now something like the LINQ to SQL designer is pretty trouble free – unless of course you have something like this fairly common scenario:

I had a class library (Data Access), and decided to add LINQ to SQL classes for a new database that was being introduced.
This class library is also ultimately being consumed by WCF web services.  I have dev, test, prod environments, so I use ASP.NET Web Deployment projects to change configuration per environment for things like appSettings and connectionStrings.

It therefore followed that I wanted to configure the LINQ DataContext connection properties in web.config.  Out of the box you’ll find your connection properties go into your Settings properties class, which gets a little bit in the way.

If you start playing around with the generated classes to change where you’re getting the connection info from then any changes in the designer will wipe them out, so a (relatively) pain free approach to setting your connection safely is the following:

Go to your LINQ to SQL designer and remove the Connection String, and set Application Settings to False

Create a new partial class to mirror your DataContext, and set the constructor to retrieve from your alternative source…

using System;
using System.Linq;
using System.Configuration;
using System.Data.Linq;

namespace CodeBureau.Services.DataAccess
{
    public partial class MyDataContext : DataContext
    {
        public MyDataContext()
            : base(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString, 
              mappingSource)
        {
            OnCreated();
        }
    }
}

This will leave all your generated code intact, but will sort out your configuration woes.