Returning datasets from LINQ to SQL in a REST/WCF service

Question

I have a WCF/REST web service that I'm considering using Linq to SQL to return database info from.

It's easy enough to do basic queries against tables and return rows, for example:

    [WebGet(UriTemplate = "")]
    public List<User> GetUsers()
    {
        List<User> ret = new List<User>(); ;
        using (MyDataContext context = new MyDataContext())
        {
            var userResults = from u in context.Users select u;
            ret = userResults.ToList<User>();
        }

        return ret;
    }

But what if I want to return data from multiple tables or that doesn't exactly match the schema of the table? I can't figure out how to return the results from this query, for example:

 var userResults = from u in context.Users
   select new  { u.userID, u.userName, u.userType, 
                 u.Person.personFirstname, u.Person.personLastname };

Obviously the resulting rowset doesn't adhere to the "User" schema, so I can't just convert to a list of User objects.

I tried making a new entity in my object model that related to the result set, but it doesn't want to do the conversion.

What am I missing?

Edit: related question: what about results returned from stored procedures? Same issue, what's the best way to package them up for returning via the service?

Answer 1

Generally speaking, you shouldn't return domain objects from a service because if you do you'll run into issues like those you're finding. Domain objects are intended to describe a particular entity in the problem domain, and will often not fit nicely with providing a particular set of data to return from a service call.

You're best off decoupling your domain entities from the service by creating data transfer objects to represent them which contain only the information you need to transfer. The DTOs would have constructors which take domain object(s) and copy whatever property values are needed (you'll also need a parameterless constructor so they can be serialized), or you can use an object-object mapper like AutoMapper. They'll also have service-specific features like IExtensibleDataObject and DataMemberAttributes which aren't appropriate for domain objects. This frees your domain objects to vary independently of objects you send from the service.

Answer 2

You can create a Complex Type and instead of returning Anonymous object you return the Complex Type. When you map stored procedures using function import, you have a option to automatically create a complex type.

Create a custom class with the properties that you need:

public class MyTimesheet
{
    public int Id { get; set; }
    public string Data { get; set; }
}

Then create it from your Linq query:

using (linkDataContext link = new linkDataContext())
{
    var data = (from t in link.TimesheetDetails
               select new MyTimesheet
               {
                   Id = t.Id,
                   Data = t.EmployeeId.ToString()
               }).ToList();
}