How to relate one Navigation property to different table based on another property value?

Question

I have a particular request. I'm working with an existing database without relations and I can't change DB structure or tables, so I'm using relations only via EntityFramework.

I have Three tables :

public class Repairs
{
    public int Id {get; set;}
    //[more properties...]

    public int IDCategory{ get; set; }
    public int IDModel{ get; set; }

    //[more properties...]

    //Navigation Properties
    public Category {get; set;}
}


public class Service
{
    public int Id {get; set;}
    public string Code {get; set;}
    public string Description {get;set;}
}


public class Category
{
    public int Id {get; set;}
    public string Name {get; set;}
}

Actually the business logic is:

• If IDModel==0 Then IDCategory 's value refers to Service table
• If IDModel >0 Then IDCategory 's value refers to Category table.

The question is : How can I translate this business logic into EntityFramework (see above for ssql example)?

Note: names and structure are approximations.

EDIT for usage example: I can't do an example with entity framework because, well, is exactly what i'm asking here and I'm relatively new to C# so can't really help this way. But I can post my actual SSQL code to do that business logic :

SELECT 
CASE reps.idModel 
    WHEN 0 THEN ser.code+ ' - ' + ser.description
    ELSE cat.name
END as 'Product'
FROM Repairs reps
LEFT OUTER JOIN Service ser on ser.id=reps.IDCategory
LEFT OUTER JOIN Category cat on cat.id=reps.IDCategory

Hope it helps!

Thanks to everybody willing to teach me how to solve this!

Answer 1

According to update, this query can be written exactly in the same manner as the SQL. Except you define proper navigation properties, which can simplify query and remove explicit joins.

var query = 
    from reps in ctx.Repairs
    join ser in ctx.Service on reps.IDCategory equals ser.Id into sj
    from ser in sj.DefaultIfEmpty()
    join cat in ctx.Category on reps.IDCategory equals cat.Id into cj
    from cat in cj.DefaultIfEmpty()
    select new
    {
        Product = reps.IDModel == 0 ? ser.Code + " - " + ser.Description : cat.Name
    };