After MUCH playing around and researching online I've finally come up with a workable solution to getting entities mapped to views working correctly. The crux of the whole process if writing an updatable view correctly. Check out my blog posting for a full write-up on how to do this. I belive that mapping entities onto updatable views is a super powerful and flexible solution which gives the best of the O/R and stored procedure worlds.
http://community.rampgroup.com/blogs/ramp_technology_group/archive/2005/05/10/16.aspx
In addition to writing the views correctly, you have to make some changes to the generated LLBLGEN code.
-
The PK/IDENTITY column in the view must be marked as an IDENTITY column in LLBLGen's properties of the view. This is so the LLBLGen knows to fetch the IDENTITY value after the insert.
-
Since the INSTEAD OF triggers do the actual work of inserting the data into the table, you must change over use @@IDENTITY instead of SCOPE_IDENTITY(). I've done several tests on this to verify it's correctness. You will only run into issues if you have additional AFTER triggers on your table that insert into tables with IDENTITY columns, or if you have multiple threads using the same DataAdapter.
So you have to change to the code...
private static PersistenceInfoBucket CreateAccountViewEntityBucket()
{
PersistenceInfoBucket toReturn = new PersistenceInfoBucket();
toReturn.AddFieldPersistenceInfo("AccountID", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "AccountID", false, (int)SqlDbType.Int, 0, 0, 10, false, "SCOPE_IDENTITY()"));
toReturn.AddFieldPersistenceInfo("Username", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "Username", false, (int)SqlDbType.VarChar, 256, 0, 0, false, ""));
toReturn.AddFieldPersistenceInfo("AccountTypeID", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "AccountTypeID", false, (int)SqlDbType.Int, 0, 0, 10, false, ""));
toReturn.AddFieldPersistenceInfo("AccountTypeName", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "AccountTypeName", false, (int)SqlDbType.VarChar, 256, 0, 0, false, ""));
toReturn.AddFieldPersistenceInfo("Password", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "Password", false, (int)SqlDbType.VarChar, 256, 0, 0, false, ""));
return toReturn;
}
to..
private static PersistenceInfoBucket CreateAccountViewEntityBucket()
{
PersistenceInfoBucket toReturn = new PersistenceInfoBucket();
toReturn.AddFieldPersistenceInfo("AccountID", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "AccountID", false, (int)SqlDbType.Int, 0, 0, 10, true, "@@IDENTITY"));
toReturn.AddFieldPersistenceInfo("Username", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "Username", false, (int)SqlDbType.VarChar, 256, 0, 0, false, ""));
toReturn.AddFieldPersistenceInfo("AccountTypeID", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "AccountTypeID", false, (int)SqlDbType.Int, 0, 0, 10, false, ""));
toReturn.AddFieldPersistenceInfo("AccountTypeName", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "AccountTypeName", false, (int)SqlDbType.VarChar, 256, 0, 0, false, ""));
toReturn.AddFieldPersistenceInfo("Password", new FieldPersistenceInfo("Test", @"dbo", "AccountView", "Password", false, (int)SqlDbType.VarChar, 256, 0, 0, false, ""));
return toReturn;
}
It would be nice if these values could be edited in the LLBLGen UI? (hint hint).
Thanks
CT