Eager Loading in Linq To Sql : Two ways

Two ways for Eager Loading in Linq to SQL

The first option is the DataLoadOptions, and the second is to utilize a ViewModel class.

DataLoadOptions

The first one is easy and simple to code, but not very good if you want to write back data to the database.

[HttpGet]
public ActionResult WebsiteImages()
{
    Web.WebDB db = new Web.WebDB();
    DataLoadOptions options = new DataLoadOptions();
    options.LoadWith<Web.Model.WebsiteImage>(x => x.Website);
    options.LoadWith<Web.Model.WebsiteImage>(c => c.WebsiteLanguage);
    db.LoadOptions = options;
  
    IEnumerable<Web.Model.WebsiteImage> websiteImages = db.WebsiteImages;
    return View(websiteImages);
}



ViewModel Class
The second option is a little bit more work, but you can easily persist data to the database on post backs. This option requires you to create a custom class to hold the reference data.

[HttpGet]
public ActionResult WebsiteImages()
{
    Web.WebDB db = new Web.WebDB();
    WebViewModel webVM = new WebViewModel();
    webVM.WebsiteImagesLinking = (from wi in db.WebsiteImages
                         select new WebsiteImagesVM
                         {
                             ID = wi.ID,
                             WebsiteImage = wi.WebsiteImage1,
                             Website = wi.Website,
                             WebsiteID = wi.WebsiteID,
                             WebsiteLanguage = wi.WebsiteLanguage,
                             Image = wi.Image,
                             ImageID = wi.ImageID,
                             MinWidth = wi.MinWidth,
                             MinHeight = wi.MinHeight,
                             MaxWidth = wi.MaxWidth,
                             MaxHeight = wi.MaxHeight,
                             Notes = wi.Notes
                         }).ToList();
      return View(webVM);
}

public List<WebsiteImagesVM> WebsiteImagesLinking { get; set; }

public class WebsiteImagesVM
    {
        public int ID { get; set; }
        public string WebsiteImage { get; set; }
        public int WebsiteID { get; set; }
        public Web.Model.Website Website { get; set; }
        public Web.Model.WebsiteLanguage WebsiteLanguage { get; set; }
        public int? ImageID { get; set; }
        public Web.Model.Image Image { get; set; }
        public int? MinWidth { get; set; }
        public int? MinHeight { get; set; }
        public int? MaxWidth { get; set; }
        public int? MaxHeight { get; set; }
        public string Notes { get; set; }
        public bool Mapped { get; set; }
    }

Getting column value - Bad vs. Good Linq to Sql

Bad
int mainDepID = db.Departments.SingleOrDefault(y => y.ID == depID).MainDepartmentID;

Good
int mainDepID = 0;

Web.Model.Department currentDepartment = db.Departments.SingleOrDefault(y => y.ID == depID);

if (currentDepartment != null)
{
   mainDepID = currentDepartment.MainDepartmentID;
}

Cool Stuff: Use array to query values using Linq to Sql

You can use an array to query for records using linq to sql...

 int[] myList = new int[] { 2625, 2666 };
 

 var myResult = from st in db.SomeTable          

               where myList.Contains(st.ID)      

               select st;

 

 

Might be useful sometimes?

Validating Linq to Sql Model

If you have created a Linq to Sql class as your model and need to add validation to it, simply create an interface and add the columns needed to validate.

ILog interface class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel.DataAnnotations;
 
namespace Test1.Models
{
    interface ILog
    {
        [Required]
        [RegularExpression(@"(\s|.){1,50}$", ErrorMessage = "Field cannot contain more than 50 characters")]
        string Name { get; set; }
 
        [Required]
        [RegularExpression(@"[0-9]*", ErrorMessage = "Field must be an integer")]
        int TableKey { get; set; }
 
        [Required]
        [RegularExpression(@"(\s|.){1,50}$", ErrorMessage = "Field cannot contain more than 500 characters")]
        string Value { get; set; }
    }
}

Log LinqToSql class:

using Test1.Models;
using System.ComponentModel.DataAnnotations;
 
namespace Log.Model
{
    [MetadataType(typeof(ILog))]
    partial class Log : ILog
    {
    }
}


Simple enough?

For larger models, I suggest creating a ViewModel and handle everything in there...