Loading a DTO from LINQ To SQL

The background

A Data Transfer Object (DTO) is a plain old CLR object (POCO) mainly used to pass data between tiers and remote calls. They’re basically classes with getters, setters, and perhaps a constructor or two, but no methods. They’re dumb objects that simply hold data.

So why would you use these? A few reasons:

  1. They flatten your data. DTO’s can contain information from a multitude of sources (database, XML, web service, etc) all neatly packaged together, including any large hierarchies of data from your relational (SQL) store.
  2. They more easily allow parameters to be passed into and out of methods, especially expensive ones like web services. You’re not forced to break a web service’s signature if you need to add/remove parameters if you pass in a DTO. The DTO can change and clients aren’t forced to immediately update. They also help keep method signatures nice and tidy.
  3. They help decouple your layers by remaining technology and location agnostic. If your relational data moves around, you simply have to modify your data layer (or your business layer if you’re passing DTO’s back for presentation reasons) to get the needed information. The consuming layer won’t care that the data is coming from somewhere else, they’re just looking at the DTO’s copy.

The setup

Let’s look at a simple scenario. We’re loading a business object and calling out to our data layer for the information, which we’ll get from SQL using LINQ to SQL. The following DTO will be passed back:

public class PersonDTO
{
	public string FirstName { get; set; }
	public string LastName { get; set; }
	public int Age { get; set; }
	public decimal HourlyWage { get; set; }
}

The code

Let’s look at the GetPerson method from the data layer, which uses LINQ to SQL to retrieve the needed information from SQL:

public PersonDTO GetPerson(int personID)
{
	using(DatabaseDataContext db = new DatabaseDataContext())
	{
		return (from p in db.Peoples
			    join pw in db.PeopleWages on p.PersonID equals pw.PersonID
			    where p.PersonID == personID
			    select new PersonDTO
			    {
					FirstName = p.FirstName,
					LastName = p.LastName,
					Age = p.Age,
					HourlyWage = pw.HourlyWage
			    }
			   ).SingleOrDefault();
	}
}

The key bit is the select new PersonDTO and its four accompanying lines. It might look a bit odd, but it’s the new object initializer syntax added to C# 3.0. The compiler is basically creating a constructor in the background, taking in the specified parameters, and setting their respective property values.

This keeps your simple select methods such as this visually clean. No need to return a LINQ object and set each property in a separate call. OK, not a huge positive, but hey, it’s the small things that count. I personally love clean, slick, code.

Also note the SingleOrDefault() call at the end, which will return a single object from the LINQ statement (which in this case is of type PersonDTO) or a default value for that object if one isn’t found (and since we’re selecting a reference type, it’d return null). SingleOrDefault() will throw an exception if more than one record is returned from SQL, but since we’re looking for a primary key here, it shouldn’t be a problem.

As a side note, one of the neat features of Visual Studio 2008 is the IntelliSense’s ability to filter the already assigned properties within the object initializer portion of the LINQ statement. For instance, notice how it’s hiding the FirstName property in the pop-up since I assigned it in the line above:

IntelliSense filtering the available properites

To wrap up, here’s the CreatePerson method, on the Person object in the business layer, that would consume the above GetPerson method:

public Person CreatePerson(int personID)
{
	using(PersonDTO personDTO = DataLayer.GetPerson(personID))
	{
		return new Person
		{
			FirstName = personDTO.FirstName,
			LastName = personDTO.LastName,
			Age = personDTO.Age,
			HourlyWage = personDTO.HourlyWage
		};
	}
}

Notice how it too is making use of the new object initializer feature.

3 Responses

  1. Alex Says:

    Hi Darrell,

    thanks a lot for your contribution concerning Dto’s and Bo’s.

    I’m trying to get the right way disposing the Linq – datacontext stuff implementing a “using…” statement. But the the consumer (here: Windowsforms DataGridView / BindingSource) says “Released object reference of datacontext is not accessible” when I’m trying to get a IQueryable “List” of type DtoCustomers. It works fine when I’m fetching a single DtoCustomer.

    What’s wrong ?

    Sincerly,
    Alex

    —- windows forms consumes the data access Method directly —-

    private void Form1_Load(object sender, EventArgs e)
    {
    this.customerBindingSource.DataSource = daCustomerManager.GetCustomer();
    }

    —- separate Data Access Class —

    ///
    /// Get List of Customer Dto
    ///
    ///
    ///
    public IQueryable GetCustomer()
    {
    using (CustomerDataContext dc = new CustomerDataContext())
    {
    return (from p in dc.Customer
    orderby p.Surname,p.Name
    select new DtoCustomer
    {
    Name = p.Name,
    Surname = p.Surname,
    EntryDate = p.EntryDate,
    ExitDate = p.ExitDate,
    Info = p.Info
    }
    ).AsQueryable();
    }
    }

    …this works fine:

    ///
    /// Get Customer Dto
    ///
    ///
    ///
    public DtoCustomer GetCustomer(int id)
    {
    using (CustomerDataContext dc = new CustomerDataContext())
    {
    return (from p in dc.Customer
    where p.Id == id
    select new DtoCustomer
    {
    Name = p.Name,
    Surname = p.Surname,
    EntryDate = p.EntryDate,
    ExitDate = p.ExitDate,
    Info = p.Info
    }
    ).SingleOrDefault();
    }
    }

  2. Darrell Mozingo Says:

    @Alex:

    That’s due to deferred execution. Linq To Sql doesn’t go to the database until the items you’re looking at are iterated over. In your single GetCustomer method, calling SingleOrDefault() forces them to be iterated right then, giving you a concrete, database detached object.

    Your other GetCustomer method, which returns the IQueryable, isn’t executed until the caller (in this case, the data binder) goes to enumerate the list. The IQueryable will then try to execute its SQL command, but since it’s already outside the GetCustomer method and its DataContext scope (which is what Linq To Sql objects use for their Unit of Work pattern), it throws the exception you’re seeing.

    Change your first GetCustomer method to return an IEnumerable, and instead of calling AsQueryable() on the Linq Select, use AsEnumerable(), which will fetch the data from the database and give you a detached enumerable for your data binder.

  3. Nisar Khan Says:

    Hi Darrell,
    can you upload a project in zip file please?

    i’m more intersted to see how many layers you have in your project

    UI
    BAL
    DAL
    Service ? (may be)
    Repository ? (not sure)?

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.