CSLA 3.5 insert\update\delete using LINQ but not stored procedures

CSLA 3.5 insert\update\delete using LINQ but not stored procedures

Old forum URL: forums.lhotka.net/forums/t/5040.aspx


Valentin_Valve posted on Tuesday, July 01, 2008

Hello, everyone.
I've been browsing ProjectTracker 3.5 and  noticed that Rocky uses sprocs to insert\update\delete and Linq to select data.
Why not use Linq for everything? Also, what is the suggested (good) way of doing this?

Thank you,

Valentin.

robert_m replied on Tuesday, July 01, 2008

Inserting and deleting is ok, but updating rows via LINQ gets somewhat awkward, basically you need to reload table row you want to update prior to actually updating it so that LINQ DataContext can detect which fields are modified, otherwise weird things can happen.....

Anyway, I find this combination quite handy. SPs give you an additional level of control over how your database data gets modified, plus if you for instance add new column to your db table and regenerate SP wrapper method (in LINQ designer), you get compile time error if you forget to handle this additional field in youe DataPortal_xxx method

justin.fisher replied on Tuesday, July 01, 2008

I've been using LINQ to SQL for the DataPortal_Fetch and stored procedures for everything else.  This provides a nice layer of abstraction that makes it easy to do things like perform a soft delete vice a hard delete, implement optimistic concurrency, or insert any auditing and logging that may be required all in a single stored procedure call. 

I have found this method to be very useful because it limits the amount of code you need to write for retrieving objects.  I used to write a stored procedure for retrieving by various fields. Adding another RetrieveBy_?whatever? involved writing out the new stored procedure and then passing in the parameters and reading the result.  Not difficult but definitely time consuming.

This combination of fetching with LINQ and performing insert\update\delete with stored procedures provides a lot of flexability in how the objects are retrieved while providing pretty tight control over what happens when inserting and updating objects.

ajj3085 replied on Tuesday, July 01, 2008

I do that as well, but I set up Linq to use stored procedures as well, so that all data access code is done via linq.

justin.fisher replied on Tuesday, July 01, 2008

Thanks you for clarrifying that ajj3085.  I left out that little detail in my previous post.  I also expose the stored procedures via LINQ and it ends up saving a massive amount of time iteracting with the database.

Before I started going through the ProjectTracker example LINQ was only a buzz word.  Seeing it in action really changed how I develop code, the time savings and increase in productivity are amazing.

ajj3085 replied on Tuesday, July 01, 2008

Ahh, I thought you called the sps directly.

I actually go a step further, and have been setting the linq objects with the required change notifications, so that I can just manipulate the data object itself and not worry about stored procedure calls.

The trick for updates is to use a dummy data object where you set the key values and any timestamp values.. then you attach to the context using the overload that takes the "current" object and the "original" (dummy) object.  Since I'm using sps, it has to set every parameter anyway.  Works pretty nicely.

Andy

justin.fisher replied on Tuesday, July 01, 2008

That sounds interesting . . . but I'm not sure I completely followed you?

Can you recommend a source where I can find some more details on the technique you described?

Like I said, I am pretty new to LINQ but even with my limited experience I can see it's potentional to save me a tremendous amount of work.

ajj3085 replied on Wednesday, July 02, 2008

Well, my update code looks something like this:

Data.Address addr, dummy;
ContextManager<MyDataContext> mgr;

addr = new Data.Address();
addr.AddressId = 10;
addr.State = "PA";
addr.Line1 = "Line 1";

dummy = new Data.Address();
addr.AddressId = 10;

using ( mgr = ContextManager<MyDataContext>.GetManager() ) {
    mgr.DataContext.Address.Attach( addr, dummy );
    mgr.DataContext.SubmitChanges();
}

The address class and context can be created using the designer.. in my case I had existing classes so I use the designer to figure out what code and attributes to add to the DataContext and the various data classes... but that's just because I'm supporting both my custom DAL and Linq.  Eventually my DAL will go away though.

The purpose of the dummy object is because Linq needs to know that the row REALLY changed from what was in the database.. so you're telling Linq what the original values were.  As I said, since I have Linq calling my insert / update / delete procs, it passes all the arguments to the Sp, so I don't really need the other values to be set in the dummy instance.

Andy

Valentin_Valve replied on Thursday, July 03, 2008

Thank you guys, very much, your help is already in code!

Valentin.

greengumby replied on Wednesday, December 10, 2008

ajj,

How are you retrieving your original values?  Below I have the DataPortal_Update method are you basically storing the original values in a private PropertyInfo?

 protected override void DataPortal_Update()
 {
     bool cancel = false;
     OnUpdating(ref cancel);
     if (cancel) return;

            using (var mgr = ContextManager<MM_V2.DAL.LINQ.Project.ProjectDataContext>
                         .GetManager((Csla.ApplicationContext.User.Identity as MMIdentity).Connectionstring, false))
            {
                var data = new MM_V2.DAL.LINQ.Project.Projects()
                {
                    IDProject = ReadProperty<Guid>(IDProjectProperty)
                };
                data.rowversion = _rowversion;
               
                mgr.DataContext.Projects.Attach(data);
  OnMemberReading(data);
  if (IsSelfDirty)
  {
    data.Description = ReadProperty<string>(DescriptionProperty);
    data.Active = ReadProperty<bool>(ActiveProperty);
    data.Parent = ReadProperty<Guid?>(ParentProperty);
  }

    OnMemberRead();

  mgr.DataContext.SubmitChanges();
  if (IsSelfDirty)
  {
    _rowversion = data.rowversion.ToArray();
  }
      }//using

      OnUpdated();
}

ajj3085 replied on Thursday, December 11, 2008

Yes, I load the ids I need in the DataPortal_Fetch.  Usually in a nullable field, although not necessarly in a PropertyInfo, since it will also be private.

Copyright (c) Marimer LLC