Entity Left Join to Replace SQL

Question

I am accustomed to using SQL left joins to get a list of all available options and determine what items are currently selected. My table structure would look like this

Table MainRecord -- recordId | mainRecordInfo
Table SelectedOptions -- recordId | optionId
Table AvailableOptions -- optionId | optionValue

and my query would look like this

SELECT optionValue, IIF(optionId IS NULL, 0, 1) AS selected
FROM AvailableOptions AS a
LEFT JOIN SelectedOptions AS s ON s.optionId = a.optionId AND a.recordId = 'TheCurrentRecord'

I am trying to replace this with Entity Framework, so I need help with both a model and a query -- they both need corrected.

public class MainRecord
{
    public int recordId { get; set; }
    public string mainRecordInfo { get; set; }
    [ForeignKey("recordId")]
    public List<SelectedOptions> selectedOptions { get; set; }
}
public class SelectedOptions
{
    public int recordId { get; set; }
    public int optionId { get; set; }
}
public class AvailableOptions
{
    public int optionId { get; set; }
    public string optionValue { get; set; }
}

Query

IQueryable<AvailableOptions> options = from o in context.AvailableOptions select o;

I can get a list of AvailableOptions, but how can I get a list and know which ones are selected?

Answer 1

If the number of selections and available options is small enough, you can do this in memory:

var selected = options.Join(record.selectedOptions, ao => ao.optionId, so => so.optionId, (a, s) => new { Available = a, Selected = s });            

selected will now be a list of objects with Available and Selected as properties and will only contain those that matched in optionId value.

If you only wish to get a pure list of AvailableOptions that match, simply chain a Select to the join:

var selected = options.Join(record.selectedOptions, ao => ao.optionId, so => so.optionId, (a, s) => new { Available = a, Selected = s })
.Select(o => o.Available);