This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
I have some through associations in my model similar to the Many-To-Many associations I had in Entity Framework which I managed to model in Lightspeed using an explicit join entity and then doing the through association on the entity. So my Employee entity has a collection on it called EmployeeGroups, which is a Many-To-Many association from Employee to EmployeeGroup, and in the group there is another collection called AccessList which is a Many-To-Many association from EmployeeGroup to EmployeeAccess. To get a list of all the employee access names for all the groups they belong to, with EF I used this query: var query = (from e in uow.Employees When I try to run this query in Lightspeed I get the following exception: System.NotSupportedException was unhandled by user code Have I done something wrong in my model perhaps, or am I supposed to structure this query differently? I am not entirely sure how I would structure it differently as I have tried a bunch of different ways and I cannot get it to work? |
|
|
Because I had to have join entities set up to model the through associations in our database, I was able to re-write the LINQ to explicitly join across the join table, with the following LINQ: var query = (from e in uow.Employees This works, but it is still not clear to me how I am supposed to be able to use Through associations in LINQ queries, because if I was able to model the through association with an auto through entity (not possible with a pure join table using a composite key), then I would have no way to write this query. So I assume there must be some way to do it, but I am just not setting it up correctly in my model perhaps? |
|
|
I thought perhaps this was related to the fact that my through association entities were composite key entities, but changing them to surrogate key entities made no difference. |
|
|
Actually I had the query wrong before. The query that works for me directly using the join entity is: var query = from m in uow.EmployeeGroupMembers But I am still not sure how this could be written to work if I use an auto-through entity, because the through association does not seem to work properly when I try to write the query using the through association, and without an explicit through entity you cannot write it as I did above. |
|
|
ThroughAssociations are not currently supported as traversals in a join situation, but you can use them in the criteria - e.g. where m.Employees.EmployeeId == id (where Employees would be the ThroughAssociation in your model). If you need to join directly you will need to join across the through entity explicitely as you have found.
Jeremy |
|
|
Is it possible to write the query I originally had without using the explicit joins? More importantly, I have the solution implemented now but we have changed our tables over to surrogate keys so now I can convert my through associations to use an auto-through entity, but if I did that, it is not clear at all if my query can be coded in LINQ with an auto through entity? BTW, related to auto through entities, it does not seem to be possible to set up the auto through entity and map it to a specific table (or map the columns explicitly)? So I assume this only works if Lightspeed controls the schema mapping and you don't need to override it, so it won't work for a legacy database if the column names and table names don't match? |
|
|
No I dont think its possible - you will need the explicit joins to get across the through association. With the auto-through entities, you can think of them as a bit of a designer convenience. At the end of the day the designer will still generate exactly the same code as with an explicit through entity which means you can still use it with your LINQ queries. The only practical difference is just that you are removing some of the clutter that you get by having the through entity on the model surface. If you need to configure any of the mappings or properties then you will want to have an explicit through entity so that the entity is available on the model surface for you. Here is a link to the docs in case this adds a bit more context around the options for you: http://www.mindscapehq.com/documentation/lightspeed/Working-with-Models-in-the-Visual-Designer/ManytoMany-Associations
Jeremy |
|
|
Ok thanks. That is about what I figured out when I played with it; auto through entities won't work if I need to map to a specific table and map specific columns in my DB. |
|