N-Tiered LinqToSql Question

Question

Hello. I am hoping you can help. I am developing a tiered website using Linq to Sql. I created a new class(or object) in DBML designer called memberState. This object is not an actual table in the database. I have this method in my middle layer:

public override IEnumerable(memberState) GetMembersByState(string @state)
{
using (BulletinWizardDataContext context = DataContext)
{
IEnumerable(memberState) mems = (from m in context.Members
join ma in context.MemberAddresses
on m.UserId equals ma.UserId
join s in context.States
on ma.StateId equals s.StateId
where s.StateName == @state
select new memberState
{
userId = m.UserID,
firstName = m.FirstName,
middleInitial = m.MiddleInitial,
lastName = m.LastName,
createDate = m.CreateDate,
modifyDate = m.ModifyDate
}).ToArray(memberState)();
return mems;
}
}

The tables in my joins (Members, States, and MemberAddresses are actual tables in my Database). I created the object memberStates so I could use it in the query above (notice the Select New memberState. When the data is updated on the web page how do I persist the changes back to the Member Table? My Member Table consists of the following columns: UserId, FirstName, MiddleInitial, LastName, CreateDate, ModifyDate. I am not sure how save the changes back to the database.

Thanks,

Answer 1

If I remember correctly, you can create a view from the different tables (Members, States, and MemberAddresses) and add that to the data context. Then any modifications to data in the view object can be saved, and linq to sql will handle the commit correctly as long as all the relationships are clearly setup/defined in both the database and in the data context.

Answer 2

If you have a Member table, the dbml will most likely contain a Member class. To update a member in the database, you will have to create a new Member object, and the Attach it to the BulletinWizardDataContext.Members collection. Something similar to the following code should the trick (I have not tested the code):

using (BulletinWizardDataContext context = DataContext)
{
    Member m = new Member() { UserId = userId };
    context.Members.Attach(m);
    m.FirstName = firstName;
    // Set other properties
    context.SubmitChanges();
}

Attach must be called before setting the properties. Also, Linq2Sql has some issues with Attach in the case where the properties of your object are set to default values (i.e. 0 for numeric values, false for booleans, null for string etc.). In this case Attach will not generate the correct SQL.

Answer 3

var m = myContext.Members.Single(m=> m.UserID == myMemState.userID);
m.FirstName = myMemState.firstName;
m.MiddleInitial = myMemState.middleInitial;
...

That would be the quick way. It does an additional roundtrip to the db, but will work well. If that's an issue for you, then do Attach like Jakob suggested. For that you have to have to do some extra steps, like reviewing the configuration for optimistic updates and make sure you have the original fields when doing the attach.