LINQ to Entites with SQL 2000 issue

Question

Hi!

I'm using Linq to Entities. I have the following query in my code, it includes left outer Join:

    var surgeonList = (from item in context.T1_STM_Surgeon.Include("T1_STM_SurgeonTitle")
                                .Include("OTER").Include("OSLP")
                                join reptable in context.OSLP on item.Rep equals reptable.SlpCode into surgRepresentative
                                where item.ID == surgeonId
                                select new
{
    ID = item.ID,
    First = item.First,
    Last = item.Last,
    Rep = (surgRepresentative.FirstOrDefault() != null) ? surgRepresentative.FirstOrDefault().SlpName : "N/A",
    Reg = item.OTER.descript,
    PrimClinic = item.T1_STM_ClinicalCenter.Name,
    Titles = item.T1_STM_SurgeonTitle,
    Phone = item.Phone,
    Email = item.Email,
    Address1 = item.Address1,
    Address2 = item.Address2,
    City = item.City,
    State = item.State,
    Zip = item.Zip,
    Comments = item.Comments,
    Active = item.Active,
    DateEntered = item.DateEntered

})
                                .ToList();

My DEV server has SQL 2008, so the code works just fine. When I moved this code to client's production server - they use SQL 2000, I started getting "Incorrect syntax near '(' ".

I've tried changing the ProviderManifestToken to 2000 in my .edmx file, then I started getting "The execution of this query requires the APPLY operator, which is not supported in versions of SQL Server earlier than SQL Server 2005." I tied changing the token to 2005, the "Incorrect syntax near '(' " is back.

Can anybody help me to find a workaround for this?

Thank you very much in advance!

Answer 1

Read these suggestions. Also, rewrite the join using associations. I can't promise this will work, but it's the way I'd attack the problem.