I'm using LLBLGen 3.1, self servicing - trying to optimize a subquery. I'm pretty awful at SQL so any advice is appreciated.

Basically I need to find the latest date for each source. In sql, I can create a single query that grabs everything in one trip to the database, but when I run the following & look at the profiler, I get a separate query for each key. So in this case, there would be 4 separate queries run:

    from key in new []{"a", "b", "c", "d"}
    select new {
        Key = key,
        Date = (from s in new LinqMetaData().Segment
                     where s.SourceKey == key
                     orderby s.CreatedOn descending
                     select s.CreatedOn).FirstOrDefault()
    }

Is there any way to make this run as a single trip to the database?

For example, here's a similar SQL query that works how I want:

SELECT DISTINCT SourceKey, (select top(1) CreatedOn from Segment s2 where s1.Id = s2.Id order by s2.CreatedOn desc) FROM Segment s1

Of course, the big difference is my SQL is doing a distinct instead of using static values. But even when I rewrite the linq to use a distinct, it runs two queries instead of one. That's better than 4, but I really want 1.

Try to implement the following:

SELECT SourceKey, Max(CreatedOn)
FROM Segment
GROUPBY SourceKey

Thanks Walaa -

You're right & I'm a dummy. I tried to simplify my problem to capture the subquery issue without polluting the question with my business logic. I'll keep digging into my code & try to come up with a better question.

You could do it this way:

var q = from s in metaData.Segment
        group s by s.SourceKey into g
        select new
        {
            SourceKey = g.Key,
            MaxCreatedDate = g.Max(x => x.CreatedOn)
        };

If you want to filter the results by your keys:

var keys = new []{"a", "b", "c", "d"};
var q = from s in metaData.Segment
        where keys.Contains(s.SourceKey)
        group s by s.SourceKey into g
        select new
        {
            SourceKey = g.Key,
            MaxCreatedDate = g.Max(x => x.CreatedOn)
        };