CODEWITHSUNDEEP
c#sqllinqlinq-to-sqllinq-to-entities
Brian W.
Brian W.

Reputation: 150

Error converting SQL with join to LINQ

I have a SQL query that I'm trying to convert to LINQ and am having trouble understanding the obscure error messages when the query is enumerated.

The SQL query (which works as intended), is:

select a.TestGuid, MIN(a.StartTime) as StartTime, COUNT(b.TestCaseId) as NumTests, COUNT(DINSTINCT a.Id) as NumScenarios
from LoadTestSummary as a
join LoadTestTestSummaryData as b
    on a.LoadTestRunid = b.LoadTestRunId
where
    a.TargetStack = env and
    a.TestGuid IS NOT NULL AND
    a.StartTime IS NOT NULL AND
    a.LoadTestRunId IS NOT NULL
group by a.TestGuid

Converting to LINQ, I get the following:

var q = from a in _context.LoadTestSummary
        where
            a.TargetStack == env &&
            a.TestGuid != null &&
            a.StartTime != null &&
            a.LoadTestRunId != null
        join b in _context.LoadTestTestSummaryData on new
        {
            LoadTestRunId = Convert.ToInt32(a.LoadTestRunId)
        } equals new
        {
             LoadTestRunId = b.LoadTestRunId
        }
        group new { a, b } by new
        {
            a.TestGuid
        }
        into g
        select new 
        {
            DateCreated = g.Min(p => p.a.StartTime),
            NumScenarios = g.Count(),
            TestGuid = g.Key.TestGuid
            NumTests = // ???
        };

Two problems I have:

1) When the query is enumerated I get a run-time error that I'm having trouble deciphering. The query works fine in Linqpad, but gives me a run-time error in my program. I am not sure what would cause this. Just staring at this makes my head hurt:

ArgumentException: Expression of type 'System.Func``2[Microsoft.Data.Entity.Query.EntityQueryModelVisitor+TransparentIdentifier``2[PerfPortal.Models.LoadTestSummary,PerfPortal.Models.LoadTestTestSummaryData],<>f__AnonymousType7``1[System.String]]' cannot be used for parameter of type 'System.Func``2[<>f__AnonymousType5``2[PerfPortal.Models.LoadTestSummary,PerfPortal.Models.LoadTestTestSummaryData],<>f__AnonymousType7``1[System.String]]' of method 'System.Collections.Generic.IEnumerable``1[System.Linq.IGrouping``2[<>f__AnonymousType7``1[System.String],<>f__AnonymousType5``2[PerfPortal.Models.LoadTestSummary,PerfPortal.Models.LoadTestTestSummaryData]]] _GroupBy[<>f__AnonymousType5``2,<>f__AnonymousType7``1,<>f__AnonymousType5``2](System.Collections.Generic.IEnumerable``1[<>f__AnonymousType5``2[PerfPortal.Models.LoadTestSummary,PerfPortal.Models.LoadTestTestSummaryData]], System.Func``2[<>f__AnonymousType5``2[PerfPortal.Models.LoadTestSummary,PerfPortal.Models.LoadTestTestSummaryData],<>f__AnonymousType7``1[System.String]], System.Func``2[<>f__AnonymousType5``2[PerfPortal.Models.LoadTestSummary,PerfPortal.Models.LoadTestTestSummaryData],<>f__AnonymousType5``2[PerfPortal.Models.LoadTestSummary,PerfPortal.Models.LoadTestTestSummaryData]])'

2) I am not quite sure how to get the COUNT(DISTINCT a.Id) into the NumTests field. It looks like this isn't supported in LINQ but it looks like other people have asked this question to so I may be able to figure it out once #1 is resolved.

Any thoughts on what's wrong here? I am not even sure exactly what the error is telling me.

All help is appreciated!

Upvotes: 0

Views: 75

Answers (1)

Gasper
Gasper

Reputation: 3030

Looking just at the SQL query and your LINQ code, I came up with something like this:

from a in LoadTestSummary
join b in LoadTestTestSummaryData 
    on a.LoadTestRunId equals b.LoadTestRunId 
where
    a.TargetStack == env &&
    a.TestGuid != null &&
    a.StartTime != null &&
    a.LoadTestRunId != null
group new { a, b } by a.TestGuid into g
select new 
{
    TestGuid = g.Key,
    DateCreated = g.Min(el => el.a.StartTime),
    NumTests = g.Select(el => el.b.TestCaseId).Count(),
    NumScenarios = g.Select(el => el.a.Id).Distinct().Count()
};

Note, that you don't need to convert LoadTestRunId to int, you may just use standard string comparision.

That horrendous error is most likely caused by grouping and comparing using anonimous objects, thou I prefer not to read that error too much as it's an eldritch abomination not ment to be seen nor comprehend by mere mortals, it seems.

Upvotes: 1

Related Questions