MultiTierLinqToSql Discussions Rss Feed

New Post: Updating entity with associate entity

kryzian — Fri, 20 Nov 2009 10:41:48 GMT

Further to this, I should also point out that if your DTO model has both ProductTypeId and ProductTypeModel properties (i.e. one an int value and one a reference to an equivalent dto model for the entity fk reference) then it will fail. If you remove the ProductTypeModel reference from the ProductModel then it will work. Also, if you remove the ProductTypeId and only update the ProductTypeModel then it will also work.

This may not suit what you need because you may actually need the FK DTO model for other purposes.

New Post: Updating entity with associate entity

kryzian — Fri, 20 Nov 2009 10:08:57 GMT

Alright, further info on this issue. It seems plenty of people are having issues with L2S and this particular problem.

We've identified where this happens now and maybe that can help us to get a good solution in place.

On the GenericRepository.Update method, it attempts to load the entity into the DataContext, change the values according to the incoming entity and then save the changes. When it tries to load the entity into the DataContext, it checks to see if the DataContext has something already loaded. In your case you have probably already loaded the entity (by doing a GetEntity elsewhere in the request) and L2S will not like any attempt to change a foreign key reference by the Id field because it already has a reference to the foreign key entity in memory (i.e. you can't change one without the other).

There may be a number of ways around this ...

1. Change the foreign key entity by creating a new one, removing the old reference and attaching this one.

2. Check out some of the Detach and Attach suggestions on other forums.

3. Try your hardest not to load the entity for update anywhere else before you do the update. :)

I'm looking into better ways of getting around this. I think we may be able to add a Detach solution to the GDL, but don't hold your breath on that.

New Post: Updating entity with associate entity

magicgumbo — Thu, 19 Nov 2009 22:19:10 GMT

Thanks Taliesins,

I'll have a play with this and get back to you.

New Post: Updating entity with associate entity

taliesins — Thu, 19 Nov 2009 11:59:20 GMT

Linq to Sql is the culprit. When ever you create a linq to sql object it has an associated datacontext attached to it. This is to record all changes that have taken place to the object . Then when you commit the changes it is actually the datacontext being committed and not the object itself. This makes sense when you start thinking about child collections and adding/removing items from it. Something has to record the diff of the object graphs (thats why we love you linq to sql).

So when you create a linq to sql object with automapper it creates the object, but it does not have the datacontext associated with it. So I would load the object with the generic repository. Then use automapper to make the appropriate changes by mapping the DTO properties to the BO properties (look up the assembler pattern, as this is what we want).You can do this by passing in both the DTO and BO into automapper map function. Datacontext will record all of the changed properties. Then use the generic repository to update the object.

New Post: Updating entity with associate entity

magicgumbo — Wed, 18 Nov 2009 15:32:16 GMT

The response time in this project is unreal.

Ok, I have been using the AutoMapper but you get an exception which on close investiagtion is throw due to the ForeignKeyReferenceAlreadyHasValueException.

New Post: Updating entity with associate entity

kryzian — Wed, 18 Nov 2009 15:22:32 GMT

I think the issue is with your mapping. Presumably, you're mapping a LinqToSql entity to a custom Product model (a DTO type model) when you read and the reverse when you update.

So, when you read, your mapper takes the entity and the association to ProductType and populates the Product with the relevant properties and the child object of ProductType based on the L2S association.

On update, the reverse mapping of ProductType isn't 100% valid because when you think about it, you wouldn't do an update on a lookup table in sql as part of a related table. So, either you remove the ProductType property in your ProductModel, or you just remove that mapping before it calls the Update method.

Hopefully this should work ... (if I understand you correctly)

New Post: Updating entity with associate entity

magicgumbo — Wed, 18 Nov 2009 15:05:36 GMT

Hi there,

I don't know if i am wide of the mark here, so forgive me if i sound like a numpty. My point is based around updating.

I have a Product table, that has a ProductTypeId (int?). The productTypeId is a FK to a ProductType table

My Product Model has a few properties but also a property ProductType.

For whatever reason I need to change the Product's ProductTypeId to be something else. erm... from 1 to 2 - from "Jam" to "Jelly" Or was previously null and now gonna be something...

I get the orginal version of the entity, make the change to the productTypeId and then call the Update passing the entity.

Now, this doesn't do as i'd hope.

The update can fail in a number of ways.

If the productTypeId was not previously null it throws a ForeignKeyReferenceAlreadyHasValueException and to get around this you'd have to set the associated entity of the ProductType with the new related data "Jelly" prior to setting the productTypeId to 2. This seems like the wrong solution as I don't want to perform x number of reads to be able to update a set (depending on the number of associated entities you have and could be updating).

If the productTypeId was previously null, you manage to avoid the previous exception, but you reach this bit of code from the .generated.cs (In the " #region Associations") which I don't quite understand. But for me, as i haven't (and don't want to) set my ProductType entity prior to the update, seems to reset my new value i want to store (changes the 2 back to a null)

if (value != null) {
      value.SupplierProductTypes.Add(this);
      _ProductTypeId = value.Id;
}
else {
     _ProductTypeId = default(int?);
}
SendPropertyChanged("ProductType");

Sorry I have gone around in circles for the last few hours with this, so i might not be 100% accruate with the details and the explaination may be a little cloudy.

So.... my question is, am i missing the point? shouldn't the model be able to just to update the fk without updating the associated entities too?

(not so magic) gumbo

New Post: Deleting from a table that contains a composite primary key field

rbates — Tue, 10 Nov 2009 21:44:32 GMT

Hi magicgumbo, can you add a single surrogate primary key to this table? That is what I would suggest, as you've spotted the GenericRepository assumes that you will use a single primary key.

On Tue, Nov 10, 2009 at 4:48 PM, magicgumbo <notifications@codeplex.com> wrote:

From: magicgumbo

Hi there,

I seem to have stumbled across an issue to which I could do with some guidence.

I have a joining table ProductCategory which is - as you could guess - just a table with a composite primary key.

When it comes to deleting an entry it seems to try and approach the delete as if the table has a single primary key, checking to make sure it is - else throws an exception.

A solution would be to write a sproc and then use that, but I thought it might be good to hear what others have done.

Surely I am not the first to come across this, so speak up :)

Thanks in advance for the advice..

Read the full discussion online.

To add a post to this discussion, reply to this email (MultiTierLinqToSql@discussions.codeplex.com)

To start a new discussion for this project, email MultiTierLinqToSql@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

New Post: Deleting from a table that contains a composite primary key field

magicgumbo — Tue, 10 Nov 2009 17:07:10 GMT

Wow. talk about a quick response.

It's a solution, not what i was expecting, but it's a fair one.

Cheers

New Post: Deleting from a table that contains a composite primary key field

taliesins — Tue, 10 Nov 2009 16:57:22 GMT

To try and keep things simple we assign one column as the primary key. You can always add a unique index for productId and categoryid if you want 100% functionality of composite primary key. The datalayer does not work composite primary keys.

Yes I know its not optimal but it works and makes your life simple in the long run. The problems start occurring when you have a join table on a join table on a join table, as you got to keep track of the primary key ids.

Hence join tables looks like this:

ProductCategory
==============
-ProductCategoryId (PK identiy)
-ProductId (FK Product.ProductId)
-CategoryId (FK Category.CategoryId)

New Post: Deleting from a table that contains a composite primary key field

magicgumbo — Tue, 10 Nov 2009 16:47:58 GMT

Hi there,

I seem to have stumbled across an issue to which I could do with some guidence.

I have a joining table ProductCategory which is - as you could guess - just a table with a composite primary key.

When it comes to deleting an entry it seems to try and approach the delete as if the table has a single primary key, checking to make sure it is - else throws an exception.

A solution would be to write a sproc and then use that, but I thought it might be good to hear what others have done.

Surely I am not the first to come across this, so speak up :)

Thanks in advance for the advice..

New Post: Lost updates

reachstu — Tue, 27 Oct 2009 13:52:15 GMT

I added a timestamp column to each table in my database (called each column "row_version"). I then modified the following code in the Generic Repository to stop the method from changing the timestamp column. I also added a method to the GenericRepository to get the Version column

protected void UpdateOriginalFromChanged(ref TEntity destination, TEntity source)
        {
            foreach (PropertyInfo pi in DatabaseProperties)
            {
                if (pi.Name != MetaVersion.Name)
                {
                    pi.SetValue(destination, pi.GetValue(source, null), null);
                }
            }
        }

private MetaDataMember _metaVersion;
        private MetaDataMember MetaVersion
        {
            get
            {
                if (_metaVersion == null)
                {
                    Type entityType = typeof(TEntity);
                    MetaTable mapping = DataContext.Mapping.GetTable(entityType);
                    _metaVersion = mapping.RowType.DataMembers.Single(d => d.IsVersion);
                }
                return _metaVersion;
            }
        }

The code below seems to work (obviously all tables must have a timestamp attribute for it to catch lost updates):

// Version1

public void Update(TEntity entity, bool submitChanges)
 {
 using (TransactionScope ts = new TransactionScope())
 {
 TEntity original = GetEntity(entity);
 UpdateOriginalFromChanged(ref original, entity);

 Type entityType = typeof(TEntity);

 PropertyInfo propertyInfo = entityType.GetProperty(_metaVersion.Name);
 Binary modifiedVersion = (Binary)propertyInfo.GetValue(entity, null);
 Binary originalVersion = (Binary)propertyInfo.GetValue(original, null);

 if (modifiedVersion != originalVersion)
 {
 throw new NotSupportedException("The row has been changed or deleted since first read.");
 }

 if (submitChanges)
 DataContext.SubmitChanges();
 ts.Complete();
 }
 }

The code below works in debug, but throws the following error when I run the test (from my initial post). This error is caught when the Attach method is called:

"An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported."

// I modified and tried this method in the GenericRepositorty class and it fails:

public void Update(TEntity entity, bool submitChanges)
 {
  using (TransactionScope ts = new TransactionScope())
 {
 ITable itbl = DataContext.GetTable(entity.GetType());
 itbl.Attach(entity, true);

My code in Version1 seems to be working, but there must be a better way to handle the concurrency check. Is there a way for an entity to be read by one DataContext and be updated by another DataContext?

New Post: Lost updates

reachstu — Mon, 26 Oct 2009 20:32:26 GMT

Thanks taliesins for your quick response. I'm taking on your recommended simple solution, using the timestamp column.

New Post: Lost updates

taliesins — Mon, 26 Oct 2009 11:12:06 GMT

To help you on your way:

        public object Insert(object item)
        {
            using (TransactionScope ts = new TransactionScope())
            {
                ITable itbl = DataContext.GetTable(item.GetType());
                itbl.InsertOnSubmit(item);
                itbl.Context.SubmitChanges();
                ts.Complete();
            }
            return item;
        }
        public object Delete(object item)
        {
            using (TransactionScope ts = new TransactionScope())
            {
                ITable itbl = DataContext.GetTable(item.GetType());
                itbl.DeleteOnSubmit(item);
                itbl.Context.SubmitChanges();
                ts.Complete();
            }
            return item;
        }

        public object Update(object item)
        {
            using (TransactionScope ts = new TransactionScope())
            {
                ITable itbl = DataContext.GetTable(item.GetType());
                itbl.Attach(item);
                itbl.Context.Refresh(RefreshMode.KeepCurrentValues, item);
                itbl.Context.SubmitChanges();
                ts.Complete();
            }
            return item;
        }

New Post: Lost updates

taliesins — Mon, 26 Oct 2009 10:59:45 GMT

Howzit! I am originally from Durban :>

I think you need to read up on optimistic and pessimistic concurrency. Linq to sql by default uses optimistic concurrency.

To get around the disconnected nature of the web we are doing something sneaky in the update method. We are loading the entity from the database and then updating its properties from the entity we are passing it to update (trying to avoid attach method here), I suspect this may be the root of your problems, as it loads the object right before it updates it (so you always have the latest version), hence the 0 concurrency errors. So the strategy we are using here is that last write wins.

Datacontext is lost as soon as the "edit" page is rendered (unless you do something crazy and serialize the object graph and *shudder* create something like asp.net form's viewstate). When submit button is hit it, it loads the entity and then makes changes to it.

To keep it simple the way I would do it is add a timestamp (sql rowversion) column and use linq to sql attach. What ever you do work on a single entity and not on an object graph (this will lead to social suicide).

We haven't figured out a way of using attach nicely in the repository (must be asp.net web form friendly). Would love someone to contribute a fix for this :>

This should make for some good reading on the topic:
http://www.west-wind.com/weblog/posts/246222.aspx#246646

        public void Update(TEntity entity, bool submitChanges)
        {
            TEntity original = GetEntity(entity);
            UpdateOriginalFromChanged(ref original, entity);

            if (submitChanges)
                DataContext.SubmitChanges();
        }

        protected void UpdateOriginalFromChanged(ref TEntity destination, TEntity source)
        {
            foreach (PropertyInfo pi in DatabaseProperties)
            {
                pi.SetValue(destination, pi.GetValue(source, null), null);
            }
        }

        private List<PropertyInfo> _databaseProperties;
        protected List<PropertyInfo> DatabaseProperties
        {
            get
            {
                if (_databaseProperties == null)
                {
                    Type entityType = typeof (TEntity);
                    MetaTable mapping = DataContext.Mapping.GetTable(typeof(TEntity));
                    _databaseProperties = mapping.RowType.DataMembers
                        .Where(x => x.DbType != null)
                        .Select(x => entityType.GetProperty(x.Name))
                        .ToList();
                }
                return _databaseProperties;
            }
        }

New Post: Lost updates

reachstu — Sun, 25 Oct 2009 13:45:58 GMT

I am a student studying 3rd year Software Development at a university in Cape Town South Africa. I am using the MultiTierLinqToSql code to help me with a project in C# ASP.Net with the MVC pattern. I have come across an issue while running tests on my application. When I debug the website and do the following manually, the behavior of the website is not as expected. Here is what I do:

In two different browsers:

Scenario 1:

User1 logs in.

User2 logs in.

User1 reads record1

User2 reads record1

User1 updates record1

User2 updates record1

All of the above succeeds, and User1's update is lost. The behavior above is what I am confused about.

My integration test is shown below. The service layer does not catch the error "row not found or changed".

// In my Web Application Test project - I am simulating a different request for each database action, as would happen in a real scenario:

[TestMethod]
        public void LostUpdatesTransactionTest()
        {
            var writer = new System.IO.StringWriter();
            var wr = new System.Web.Hosting.SimpleWorkerRequest("", "", "", "", writer);

            HttpContext context1 = new HttpContext(wr);
            HttpContext context2 = new HttpContext(wr);
            HttpContext context3 = new HttpContext(wr);
            HttpContext context4 = new HttpContext(wr);
            HttpContext context5 = new HttpContext(wr);
            HttpContext context6 = new HttpContext(wr);
            var controller1 = new CompanyController();
            var controller2 = new CompanyController();

            HttpContext.Current = context1;
            Company company1 = new Company();
            company1.name = "NameOriginal";

            var result1 = (RedirectToRouteResult)controller1.Create(company1);
            Assert.AreEqual("List", result1.RouteValues["action"]);

            HttpContext.Current = context2;
            var result2 = (ViewResult)controller1.Details(company1.id, 1);
            Company company2 = (Company)result2.ViewData.Model;

            HttpContext.Current = context3;
            var result3 = (ViewResult)controller2.Details(company1.id, 1);
            Company company3 = (Company)result3.ViewData.Model;

            HttpContext.Current = context4;
            company2.name = "NameChange1";
            HttpContext.Current = context2;
            company3.name = "NameChange2";

            HttpContext.Current = context5;
            controller1.Edit(company2);
            HttpContext.Current = context6;

            var result = (ViewResult)controller2.Edit(company3);

            Assert.AreEqual("Edit", result.ViewName);
            Assert.AreEqual("row not found or changed", result.ViewData["database_error"]);
        }

// In my Web Application project:

public class CompanyController : Controller
    {
        private ICompanyService _service;
 
        public CompanyController()
        {
            _service = new CompanyService(new ModelStateWrapper(this.ModelState));
        }

        [Authorize(Roles = "Admin")]
        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult Edit([Bind]Company entity)
        {
            try
            {
                bool valid = _service.Update(entity);
                if (valid)
                {
                    return RedirectToAction("List", new { status = "Company Successfully edited" });
                }
                else
                {
                    return View("Edit", entity);
                }
            }
            catch (System.NotSupportedException e)
            {
                SetError(e.Message);
                return View("Error");
            }
        }

public ActionResult Details(int id)
        {
            try
            {
                Company entity = _service.GetEntity(id);
                return View("Details", entity);
            }
            catch (Exception e)
            {
                SetError(e.Message);
                return View("Error");
            }
        }

// In my Service Layer project:

public class CompanyService : ICompanyService
    {
        private IValidationDictionary _validatonDictionary;
        private ICompanyRepository _repository;
        private IRepsoitoryFactory repository_factory;
 
        public CompanyService(IValidationDictionary validationDictionary)
        {
            repository_factory = new RepsoitoryFactory();
            _repository = (ICompanyRepository)repository_factory.GetRepository <ICompanyRepository>("DefaultCompanyRepository");
            _validatonDictionary = validationDictionary;
        }
 
public Company GetEntity(int id)
        {
            return _repository.GetEntity(id);
        }

public bool Update(Company entity)
        {
            if (!ValidateEntity(entity))
            {
                return false;
            }
            try
            {
                _repository.Update(entity);
            }
            catch (Exception e)
            {
                _validatonDictionary.AddError("database_error", e.Message);
                return false;
            }
            return true;
        }

// In my Data Layer project

public partial class CompanyRepository : GenericRepository<Company, LaraLINQDataContext>, ICompanyRepository
{
}

I know this has to do with object tracking, but don't know what to do or where to look to solve this problem. Any help would be much appreciated. Thanks in advance. What would I have to do to make my application track changes to the entities? I want my application to be able to prevent lost updates as in Scenario 1 shown above.

New Post: VB Version

taliesins — Mon, 05 Oct 2009 08:55:31 GMT

Yes its totally possible. The T4 templates in this project come from another codeplex project, which contain a c# and vb version. We just modify them to include the generation of Repositories. It should be trival to implement.

The project location is:

http://l2st4.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=31180

Then you could use a c# to vb.net converter for the rest of the classes:

http://www.developerfusion.com/tools/convert/csharp-to-vb/

Regards
Taliesin

New Post: VB Version

caelanadriyel — Fri, 02 Oct 2009 16:58:51 GMT

Is there a VB version of this very excellent project? I would love to be able to use the T4 template with VB.

New Post: Use it in a desktop application

taliesins — Thu, 01 Oct 2009 09:48:15 GMT

Yes its possible to use it in a desktop application but difficulties will arise as you will have to handle concurrency. So I would not recommend doing it unless you have a clear strategy on how to handle concurrency conflicts on the database.

New Post: Use it in a desktop application

taliesins — Thu, 01 Oct 2009 09:48:15 GMT