Subsonic 3, how to CRUD using LinqTemplates?

Question

I am new to Subsonic, and it seems that I cant find out a natural way to do CRUD operations using the LINQ template classes. I guess in ActiveRecord, you could:

Product p = new Product(); 
p.ProductCode = "xxx"; 
p.Add();

Using the LINQTemplate generated classes however, how can I do the same thing? I can only use something like this below to insert a product object:

db.Insert.Into<UnleashedSaaS.PRODUCT>(prod => prod.Code, prod => prod.Description).Values("Product1", "Product1 Desc").Execute();

Who could kindly give me some hints? I'd really appreciate it.

Answer 1

All the CRUD happens in SubSonicRepository, which you can derive from. For example, I would have a class like this:

public class ProductRepository : SubSonicRepository<Product> {

    public ProductRepository() : base(new NorthwindDB()) { }

    // need this here because base doesn't expose the DB class that I know of
    protected NorthwindDB _db;
    protected NorthwindDB DB {
        get {
            if (_db == null) _db = new NorthwindDB();
            return _db;
        }
    }

    public void Save(Product product) {
        if (product.ProductId == 0) {
            Add(product); // Add is part of SubSonicRepository
        } else {
            Update(product);
        }
    }

    public void Delete(Product product) { ... }

    public List<Product> ListAll() {
        var products = from p in DB.Products
                       select p;

        return products.ToList();
    }

    public Product GetById(int id) {
        return DB.GetByKey(id);
    }
}

And so on. It's nice because you can consolidate all your data access methods in one place. If you have Sprocs, they're generated as methods on DB as well.

When I get time I'm going to work on adding a Save method to SubSonicRepository directly so you don't have to do the check yourself to see which method (Add or Update) to call.

Answer 2

I have modified the Classes.tt file to include:

public partial class <#=tbl.ClassName#>Repository : SubSonicRepository<<#=tbl.ClassName#>>
    {
     public <#=tbl.ClassName#>Repository() : base(new <#=DatabaseName#>DB()) { }
    }

Insert that bunch of lines between

<# foreach(Table tbl in tables){#>

and

/// <summary>

right at the top, near the namespace declaration, in my file it can be inserted in line 18.

The last thing to do is to add another "using" statement, in line 10, the next line after System.Linq statement. Now it should look like:

using System.Linq;
using SubSonic.Repository;

That will generate a repository to give you access to basic functionality, but can be modified in another partial class.

Hope that helps.