LINQ to SQL in ASP.NET MVC and Repository pattern

Question

I'm trying to follow a tutorial from the Asp.NET MVC website which uses LINQ to Entities but I decided to use LINQ to SQL instead. I'm at the point where a new table is created called Groups which has a relationship to the Contacts table. Esentially it's a One to Many relationship where a Group can have many contacts and a Contact can only have 1 Group. Please see below the example code with CRUD operations.

I'm not sure how to implement this in LINQ to SQL. For example, how do you do this in LINQ to SQL:

return _entities.GroupSet.Include("Contacts").FirstOrDefault();

Are you supposed to do a JOIN for the two tables or is there another way?

Example CODE:

using System.Collections.Generic;
using System.Linq;
using System;

namespace ContactManager.Models
{
    public class EntityContactManagerRepository : ContactManager.Models.IContactManagerRepository
    {
        private ContactManagerDBEntities _entities = new ContactManagerDBEntities();

        // Contact methods

        public Contact GetContact(int id)
        {
            return (from c in _entities.ContactSet.Include("Group")
                    where c.Id == id
                    select c).FirstOrDefault();
        }

        public Contact CreateContact(int groupId, Contact contactToCreate)
        {
            // Associate group with contact
            contactToCreate.Group = GetGroup(groupId);

            // Save new contact
            _entities.AddToContactSet(contactToCreate);
            _entities.SaveChanges();
            return contactToCreate;
        }

        public Contact EditContact(int groupId, Contact contactToEdit)
        {
            // Get original contact
            var originalContact = GetContact(contactToEdit.Id);

            // Update with new group
            originalContact.Group = GetGroup(groupId);

            // Save changes
            _entities.ApplyPropertyChanges(originalContact.EntityKey.EntitySetName, contactToEdit);
            _entities.SaveChanges();
            return contactToEdit;
        }

        public void DeleteContact(Contact contactToDelete)
        {
            var originalContact = GetContact(contactToDelete.Id);
            _entities.DeleteObject(originalContact);
            _entities.SaveChanges();
        }

        public Group CreateGroup(Group groupToCreate)
        {
            _entities.AddToGroupSet(groupToCreate);
            _entities.SaveChanges();
            return groupToCreate;
        }

        // Group Methods

        public IEnumerable<Group> ListGroups()
        {
            return _entities.GroupSet.ToList();
        }

        public Group GetFirstGroup()
        {
            return _entities.GroupSet.Include("Contacts").FirstOrDefault();
        }

        public Group GetGroup(int id)
        {
            return (from g in _entities.GroupSet.Include("Contacts")
                       where g.Id == id
                       select g).FirstOrDefault();
        }

        public void DeleteGroup(Group groupToDelete)
        {
            var originalGroup = GetGroup(groupToDelete.Id);
            _entities.DeleteObject(originalGroup);
            _entities.SaveChanges();

        }

    }
}

Answer 1

You need to specify some DataLoadOptions to create the join for you:

So to do this, you have to create a DataContext for each type of query with the correct DataLoadOptions:

var db = new WhateverDbDataContext();
DataLoadOptions options = new DataLoadOptions();
db.LoadOptions = options;
options.LoadWith(x => x.Contacts);

return db.SomeTable.FirstorDefault();

Answer 2

Linq to sql does not support the Include method. If you don't care if the relationship is lazy loaded, then you don't have to do anything. If you want it to be eager loaded, then you have use the more convoluted DataLoadOptions.

See this article:

http://blog.stevensanderson.com/2007/12/02/linq-to-sql-lazy-and-eager-loading-hiccups/