LINQ with 3 Tier

Question

Currently I am working on the design of my degree project. Some days ago I began studying LINQ. I found it was interesting and planned to use it in my project but now I am getting confused at some point.

When I add the LINQ to SQL class it auto generates entities classes against each table in database.

Suppose I have two tables in database:

User
Projects
UserProjects (joint table)

and a joint table that represents which user is associated with which project.

LINQ to SQL class auto generates these three classes for me. Now shall I create separate (User and Project) classes as Business Object or use these auto generated entities?

Also, to use database functionality we are required to use 3 tier architecture. Can I directly call LINQ DAL method from my BLL or do I need to create separate DAL which will call a method of the LINQ DAL??

class UserBLL

{
    public void saveUser(String username, String password)
    {
         // here I am calling LINQ DAL from by BLL
         UserDataContext db = new UserDataContext();
         User u =new User {Username = username, Password = password};
        db.user.InsertOnSubmit(u);
       db.SubmitChanges();
    }

}

Is the above method calling sequence fine?

Answer 1

I normally create a class scoped datacontext for each of my BLL objects.

I also create 2 constructors, 1 that creates a datacontext and another that accepts a datacontext. The second is so that I can pass around a datacontext from other BLL objects.

This allows you to perform database operations on objects that may have come from 2 different BLL objects whilst still retaining a nice separation of concerns. You will also need to expose the datacontext as public readonly so that it can be passed around.

Something to note is that a DataContext object doesn't have to be explictly disposed, more info here. But basically the DataContext will only live for the lifetime of the BLL object. You can explicitly dispose of it though if you really need to free up the resources (i.e. you've finished tracking changes).

e.g.

public class UserBLL
{
    private readonly UserDataContext context;

    public UserBLL() : this(new UserDataContext())
    {
    }

    public UserBLL(UserDataContext context)
    {
        this.context = context
    }

    public UserDataContext Context { get { return context; } }

    public void saveUser(String username, String password)
    {
         // here i am callsing LINQ DAL from by BLL
         User u = new User {Username = username, Password = password};
         Context.Users.InsertOnSubmit(u);
         Context.SubmitChanges();
    }
}

Answer 2

Linq To SQL is great for single tier design. Not so great for a disconnected model or multi tier environment.

The above code only inserts a single User into the database. If you fire up MSSQL SQL Server Profiler or connect up the log to the output in visual studio. You should see

//Hookup the log to the output in visual studio
using (var db = new UserDataContext()) {
    db.Log = Console.Out;
}

INSERT INTO User VALUES (value1, value2, value3,...)

To update the the user your code should look at somthing like

public void UpdateUser(String username, String password, int userId)
{
     using (var db = new UserDataContext()) {
         //Get Row From Database Marching Id
         var user = db.user.Single(p => p.Id = userId);
         //Update Values
         user.Username = username;
         user.Password = password;
         //Save To The Database
         db.SubmitChanges();
     }
}

//Get All Users From Database
public IEnumerable<User> GetAllUsers()
{
     using (var db = new UserDataContext()) {
         //Get Row From Database Matching Id
         var users = from user in db.user
                    select user;
         return users.ToList();
     }
}

//To display the data just enumerate through the enumeration that is returned.
var users = BusinessInstance.GetAllUsers();
foreach (var user in users) {
    //user is the current item
}

You should make your that you are using your database contract every time you do a unit of work. (because the database context using transaction by default, and this can get ugly, don't bother about performance with constructing the database context!)

Usually when you work with a multi tier environment, you would create a seperate POCO's when passing them over the wire(network).

NCommon is a great abstraction for Linq to Sql, should handle business validation and rules.

Note. Its good practice to hash password values in a database.

Check out ScottGu's blog for a quick q&a and basics on linq