How can I convert this SQL Server stored procedure to a linq expression? I've got a couple of mistakes but don't know how to fix them.

Here is the stored procedure:

    @MatterNumber NVARCHAR(20)
AS
    DECLARE @ClientNumber INT = (SELECT TOP 1 LeadPlaintiffNumber 
                                 FROM [dbo].[vw_cmp_case_numbers] 
                                 WHERE MatterNumber = @MatterNumber)

    SELECT DISTINCT 
        defendantid, 
        defendantcode, 
        defendantname DefendantName 
    FROM   
        (SELECT DISTINCT 
             fmrp.employerid DefendantId, 
             fmrp.employercode DefendantCode, 
             fmrp.employername DefendantName 
         FROM   
             vw_mpid_records fmr 
         LEFT JOIN 
             vw_mpid_records_products fmrp ON fmr.recordid = fmrp.recordid 
         INNER JOIN 
             vw_cmp_event_history fceh ON fmr.jobsitecode = fceh.jobsitecode 
                                       AND fmr.startdate < = fceh.enddate 
         WHERE  
             fceh.clientnumber = @ClientNumber 
             AND fmrp.employerid IS NOT NULL 
             AND fmrp.employercode IS NOT NULL 
         GROUP BY 
             fmrp.employerid, fmrp.employercode, fmrp.employername) yyy 
ORDER BY 
    defendantname 

Here is what I have so far for the linq but there is a error at

fmr.StartDate <= fceh.EndDate

and then I'm not sure about the group by as well

var @clientNumber = (from ccn in context.VwCmpCaseNumbers where ccn.MatterNumber == text select ccn).Take(1);

var innerQuery = from fmr in context.VwMpidRecords
                 join fmrp in context.VwMpidRecordsProducts on fmr.Id equals fmrp.Id
                 into gj
                 from x in gj.DefaultIfEmpty()
                 join fceh in context.VwCmpEventHistorys on fmr.JobsiteCode equals fceh.JobsiteCode && fmr.StartDate <= fceh.EndDate
                 where fceh.ClientNumber = @clientNumber &&
                       fmrp.EmployerID != null && 
                       fmrp.EmployerCode != null
                 group fmrp.by fmrp.EmployerID && fmrp.EmployerCode && fmrp.EmployerName

var outerQuery = (from r in innerQuery 
                  select new 
                         {
                             EmployerId = r.EmployerID,
                             EmployerCode = r.EmployerCode,
                             EmployerName = r.EmployerName
                         }).OrderBy(obj => obj.DefendantName);

var viewModel = outerQuery.Select(obj => new SelectOption 
                                             {
                                                   Text = obj.DefendantCode,
                                                   Value = obj.DefendantId,
                                             });

Here are the lines that show the errors

enter image description here

1 Answers

1
StriplingWarrior On

LINQ doesn't allow you to "join" on any criteria you want: a join can only have a [this] equals [that] form. Move any criteria that doesn't match that pattern into a where clause. (This will not impact performance of the SQL query.)

Also, group by values need to be contained in a single (anonymous) object, not &&ed together.

var innerQuery = from fmr in context.VwMpidRecords
join fmrp in context.VwMpidRecordsProducts
on fmr.Id equals fmrp.Id
join fceh in context.VwCmpEventHistorys
on fmr.JobsiteCode equals fceh.JobsiteCode
where fmr.StartDate <= fceh.EndDate
where fceh.ClientNumber = @clientNumber
where fmrp.EmployerID != null && fmrp.EmployerCode != null
group fmrp by new {fmrp.EmployerID, fmrp.EmployerCode, fmrp.EmployerName};