Many to Many relationship

Hey there..

I'm a new user to LightSpeed and so far I like it very much. *grins* I haven't worked much with SQLite, so I am unable to figure out how I would go about to make an Many to Many relationship between two tables.

Normally I would make an extra Table which consists of Id's for both of the tables that needs to be linked, for example:

Products (Contains Id), Tags (Contains Id field), Products_Tags (Contains Id, ProductId, TagId)

How would I go about to tie that into LightSpeed and use it ? Can I do it through the Model Designer or do I need to write the class myself..

Any help would be appreciated, also even if it's just to point me to the right document section..

 Thanks for reading.

 I've found a few examples here on the forum, with bits and pieces of information on how it should be done, but I just haven't been able to get it to work..

I either get to the problem where it says that it cannot find the reverse association for what I am trying to do or, I get this one: "Circular associations are not supported by LightSpeed: [UsersTags.Users and User.UserTags]"

So, if anyone could, please, post an complete sample on how to setup a many-to-many association with included database schema and source files, I would really really love that.

 I have attached a few files in a Zip, with a DB Schema that shows how the database is build up, there are no foreign keys in that DB..

 So, any hints, any at all, would be really really appreciated. *chuckles*

 

I think your problem may be that your UserTags class contains EntityCollections for Users and Tags.  These should be EntityHolders (because a single UserTag ties together *one* user and *one* tag).

You will also find that the User.UserTags and Tag.UserTags collections need to be marked as EagerLoad for through associations to work properly.

I've attached a very simple console application project (including a suitable SQLite database and the schema and data files to recreate it) that shows the absolute basics of through associations.  Hopefully this will be enough to get you started!

 Thank you very very much.. This is exactly what I was looking for and after reading your post, especially about the EntityHolder instead of the Collection, I can see that, that makes perfect sense. *chuckles*

Thank you very much again Ivan..

 

 Ivan: Thanks again for your time, though I ran into a problem when I tried to insert a new Tag into the Database..

The first try got aborted with an "No such table KeyTable exists", until I changed the the IdentityMethod on the context into IdentityColumn, but now it gives the new tag an Id of -2, is that due to the sample data inserted into the DB ?

I've just tried to recreate the db from scratch without the sample data, using the schema you provided, but now when trying to insert a new tag like this:

var workUnit = context.CreateUnitOfWork();

Tag oTag = new Tag { Name = "test" };
workUnit.add(oTag);
workUnit.SaveChanges();

It spits out an error: "Abort due to constraint violation Tags.Id may not be NULL"
And when I look in the Locals window, I can see that it set the new Tags Id to be -4 *chuckles*

Is there anyway around that ? Do I need to use a different method of inserting when using ThroughAssociations ? Selecting data from the db works fine, it's inserting it that doesn't work..

 Since I cannot edit, sorry for double posting.. But after checking on the forum, it seems that the negative Id is correct, since it's the representation that LightSpeed is doing to indicate new things, but the error I have posted in the post above, is still puzzling me..

Because LightSpeed should take care of setting the Id so that it's not null, but something seems to break. *smiles*

 Also, I've added this, to the Model.cs class, to ease the use of LINQ (after the documentation) and I've changed the project to use .NET framework 3.5 instead of 2.0, if that changes anything:

  public class SampleUnitOfWork : UnitOfWork
  {
      public IQueryable<User> Users
      {
          get { return this.Query<User>(); }
      }

      public IQueryable<Tag> Tags
      {
          get { return this.Query<Tag>(); }
      }
  }


Also the creation of the context looks like this now:

        public static LightSpeedContext<SampleUnitOfWork> context = new LightSpeedContext<SampleUnitOfWork>
        {
            DataProvider = DataProvider.SQLite3,
            ConnectionString = @"Data Source=..\..\m2m.db3",
            IdentityMethod = IdentityMethod.IdentityColumn,
            PluralizeTableNames = true,
        };

I look forward to your reply and thanks again for the help.

 

 Sorry for posting again, but it seems that I've found the error. *grins*

I was bored, since I couldn't get it to work, so I reread the documentation about IdentityMethods and then I saw the problem..

If I should use the IdentityMethod called IdentityColumn, then I need to have the Id columns in the DB set to autoincrement, which they weren't and hence the call to the DB returned NULL as Id..

But since it seems that it gives poor performance to use that IdentityMethod, I implemented the KeyTable method instead and now it works perfectly..

So thanks again for the help Ivan, I hope this post, can clear some headaches for other newcommers to Lightspeed..

Awesome product.. I love Lightspeed so far. :)

 

Sounds like you're all sorted now, is that right?  Identity methods do seem to be something that people have a bit of trouble with and we're not really sure what more we can do to clarify them.  We like KeyTable as a default because it gives good performance and works on all databases, but it does involve that extra bit of database setup that caught you out in your first attempt at inserting.  Maybe we just need to improve the error message to direct people to the KeyTable setup scripts.

Just on the performance side, if you prefer the IdentityColumn method, the performance issue with this will only hit you if you are inserting large numbers of entities (because it prevents LightSpeed from batching).  If you are inserting entities one at a time then IdentityColumn should be fine.

 Thank you very much again for the post Ivan.. Yes, I got it all sorted out now, I'm still learning how to write the model classes myself, but I've started to try my hand at writing them completely from scratch, without using the model designer..

I've even tried to look, in more detail, on the designers code, just to see how it all works, when there was something that I couldn't get to work in my own attempt..

On the documentation side, yea, more clear error messages in the exceptions that the framework throws, would be a really good idea, since it was rather hard to track down, where the specific error was. *smiles*

Also, maybe a hand written documentation, that shows how to implement an table completely from ground up, with full source code (maybe even have the source listed in the documentation section), would really really help newcommers to the framework out, because the examples that there are in the documentation right now are a bit sketchy and they seem to be a bit outdated, since it says in there, that you should, for example, create some associations with a public variable without any properties instead of making the association into a private variable with a public property like the designer does. *smiles*

Thank you again for all your help and let me know, if there is anything I can do to help out.

 

Hi Ivan,

[quote user="ivan"]I think your problem may be that your UserTags class contains EntityCollections for Users and Tags.  These should be EntityHolders (because a single UserTag ties together *one* user and *one* tag).[/quote] 

There is away to model these relationship in the DSL Designer?

Regards,

Johan

 

The designer doesn't directly support ThroughAssociations, but you can of course model the underlying one-to-many associations.  In SilverDK's example, you would:

* Use the designer to create a one-to-many association from User to UserTag, and mark it as EagerLoad

* Use the designer to create a one-to-many association from Tag to UserTag, and mark it as EagerLoad

* Create a partial class for User, add a ThroughAssociation field and property, and add a constructor which initialises the ThroughAssociation with the _userTags collection.

* And similarly for Tag.

 

Hi Ivan,

I did look at SilverDK's example and saw what I had to do just wonder if there was a way to do it with the DSL.

Thank you for your quick response.