How to use LINQ To SQL in an N-Tier Solution?

Question

Now that LINQ to SQL is a little more mature, I'd like to know of any techniques people are using to create an n-tiered solution using the technology, because it does not seem that obvious to me.

Answer 1

Hm, Rockford Lhotka sad, that LINQ to SQL is wonderful technology for fetching data from database. He suggests that afterwards they'll must to be bind to "reach domain objects" (aka. CSLA objetcs).

Seriously speaking, LINQ to SQL had it's support for n-tier architecture see DataContext.Update method.

Answer 2

You might want to look into the ADO .Net Entity Framework as an alternative to LINQ to SQL, although it does support LINQ as well. I believe LINQ to SQL is designed to be fairly lightweight and simple, whereas the Entity Framework is more heavy duty and probably more suitable in large Enterprise applications.

Answer 3

Seriously speaking, LINQ to SQL had it's support for n-tier architecture see DataContext.Update method

Some of what I've read suggests that the business logic wraps the DataContext - in other words you wrap the update in the way that you suggest.

The way i traditionally write business objects i usually encapsulate the "Load methods" in the BO as well; so I might have a method named LoadEmployeesAndManagers that returns a list of employees and their immediate managers (this is a contrived example) . Maybe its just me, but in my front end I'd rather see e.LoadEmployeesAndManagers() than some long LINQ statement.

Anyway, using LINQ it would probably look something like this (not checked for syntax correctness):

var emps = from e in Employees
                join m in Employees
                on e.ManagerEmpID equals m.EmpID
                select new
                          { e,
                            m.FullName
                          };

Now if I understand things correctly, if I put this in say a class library and call it from my front end, the only way I can return this is as an IEnumerable, so I lose my strong typed goodness. The only way I'd be able to return a strongly typed object would be to create my own Employees class (plus a string field for manager name) and fill it from the results of my LINQ to SQL statement and then return that. But this seems counter intuitive... what exactly did LINQ to SQL buy me if I have to do all that?

I think that I might be looking at things the wrong way; any enlightenment would be appreciated.

Answer 4

"the only way I can return this is as an IEnumerable, so I lose my strong typed goodness"

that is incorrect. In fact your query is strongly typed, it is just an anonymous type. I think the query you want is more like:

var emps = from e in Employees
            join m in Employees
            on e.ManagerEmpID equals m.EmpID
            select new Employee
                      { e,
                        m.FullName
                      };

Which will return IEnumerable.

Here is an article I wrote on the topic.

Linq-to-sql is an ORM. It does not affect the way that you design an N-tiered application. You use it the same way you would use any other ORM.

Answer 5

LINQ to SQL doesn't really have a n-tier story that I've seen, since the objects that it creates are created in the class with the rest of it, you don't really have an assembly that you can nicely reference through something like Web Services, etc.

The only way I'd really consider it is using the datacontext to fetch data, then fill an intermediary data model, passing that through, and referencing it on both sides, and using that in your client side - then passing them back and pushing the data back into a new Datacontext or intellgently updating rows after you refetch them.

That's if I'm understanding what you're trying to get at :\

I asked ScottGu the same question on his blog when I first started looking at it - but I haven't seen a single scenario or app in the wild that uses LINQ to SQL in this way. Websites like Rob Connery's Storefront are closer to the provider.

Answer 6

@liammclennan

Which will return IEnumerable. ... Linq-to-sql is an ORM. It does not affect the way that you design an N-tiered application. You use it the same way you would use any other ORM.

Then I guess I am still confused. Yes, Linq-to-Sql is an ORM; but as far as I can tell I am still littering my front end code with inline sql type statements (linq, not sql.... but still I feel that this should be abstracted away from the front end).

Suppose I wrap the LINQ statement we've been using as an example in a method. As far as I can tell, the only way I can return it is this way:

public class EmployeesDAL
{
    public IEnumerable LoadEmployeesAndManagers()
    {
            MyCompanyContext context = new MyCompanyContext();

            var emps = from e in context.Employees
            join m in context.Employees
            on e.ManagerEmpID equals m.EmpID
            select new
                      { e,
                        m.FullName
                      };

            return emps;
    }

}

From my front end code I would do something like this:

EmployeesDAL dal = new EmployeesDAL;
var emps = dal.LoadEmployeesAndManagers();

This of course returns an IEnumerable; but I cannot use this like any other ORM like you say (unless of course I misunderstand), because I cannot do this (again, this is a contrived example):

txtEmployeeName.Text = emps[0].FullName

This is what I meant by "I lose strong typed goodness." I think that I am starting to agree with Crucible; that LINQ-to-SQL was not designed to be used in this way. Again, if I am not seeing things correctly, someone show me the way :)

Answer 7

OK, I am going to give myself one possible solution.

Inserts/Updates were never an issue; you can wrap the business logic in a Save/Update method; e.g.

public class EmployeesDAL
{
    ...
    SaveEmployee(Employee employee)
    {
        //data formatting
        employee.FirstName = employee.FirstName.Trim();
        employee.LastName = employee.LastName.Trim();

        //business rules
        if(employee.FirstName.Length > 0 && employee.LastName.Length > 0)
        {
            MyCompanyContext context = new MyCompanyContext();

            //insert
            if(employee.empid == 0)
             context.Employees.InsertOnSubmit(employee);
            else
            {
              //update goes here
            }

            context.SubmitChanges();


        }
        else 
          throw new BusinessRuleException("Employees must have first and last names");
     }
 }

For fetching data, or at least the fetching of data that is coming from more than one table you can use stored procedures or views because the results will not be anonymous so you can return them from an outside method. For instance, using a stored proc:

    public ISingleResult<GetEmployeesAndManagersResult> LoadEmployeesAndManagers()
    {
        MyCompanyContext context = new MyCompanyContext();

        var emps = context.GetEmployeesAndManagers();

        return emps;
    }