Left OuterJoin in Entity/Linq

Question

So I am deeply confused. I have two tables, one is locations assignments which consists of: location id, type and type id. I also have a table called services, which consists of name, id , description and icon.

The idea is to say, get me back all 13 services, from that we create 13 checkboxes. then we say, check the location assignments table, if this services (based on type, id and location id) matches a service in that list, check the checkbox, else leave it unchecked.

What I ahve so far is:

    public static IEnumerable<Constants.Assignable> getAllService(int id)
    {
        List<Constants.Assignable> assign = new List<Constants.Assignable>();

        using (var db = new Context())
        {
            var serv =  from s in db.Services
                        join la in db.LocationAssignments on s.id equals la.typeId into LocationAssignments
                        from la in LocationAssignments
                        where la.locationId == id && s.id == la.typeId && la.type == Constants.SERV
                        select s;

            foreach(var s in serv)
            {

                assign.Add(new Constants.Assignable(){
                    id = s.id, name = s.name
                });
            }

            return assign;
        }
    }

which returns me, currently, two services, when it should return me 13. So there is something wrong with my join.

from there we do:

    <h3 class="muted">Services Nearby</h3>
    IEnumerable<UFA.Location.Core.Constants.Assignable> ServicesNearby = UFALocationApp.Helpers.LocationHelper.QueryHelper.getAllServicesNearby(Model.id);
    foreach (var servicenb in ServicesNearby)
    {
        <div class="control-group">
            <label class="control-label" for="serviceNearBy">
            @servicenb.name
            </label>
            <div class="controls">
                <input type="checkbox" id="Locationservice" value="@servicenb.id" name="serviceNB" checked="@(servicenb.assigned ? "checked" : "")" />
            </div>
        </div>
    }

which prints out the two check boxes that are, in this case checked. there should be 11 more that are unchecked.

What do I have to change in my query to say: get me all services and only check off the ones associated with this location?

duplicate of http://stackoverflow.com/questions/3404975/left-outer-join-in-linq — gunr2171, May 13 '13 at 20:34

score 0 · Answer 1 · answered May 13 '13 at 19:42

To make it a LEFT JOIN, you need to use DefaultIfEmpty(), it seems that the component you're missing to make this work;

var serv =  from s in db.Services
            join la in db.LocationAssignments on s.id equals la.typeId 
                into LocationAssignments
            from la in LocationAssignments.DefaultIfEmpty()
            where la.locationId == id && s.id == la.typeId 
               && la.type == Constants.SERV
            select s;

score 0 · Answer 2 · answered May 13 '13 at 22:00

If I read you well you're after something like this:

var assign = (from s in db.Services
              select new Constants.Assignable
              {
                  id = s.id, 
                  name = s.name,
                  checked= db.LocationAssignments.Any(la => la.typeId == s.id)
              }).ToList();

Now you can change the value of checked by clicking the checkbox and process the changes when you post back.

Left OuterJoin in Entity/Linq

2 Answers2