Hi, I need to configure a conditional many-to-many relationship, and I'm hoping LLBLGen can handle it. I'm using LLBLGen v4.2 against Entity Framework 6.0.

Basically I have three tables in my database

Table Sites
(
    SiteID int
)

Table Sites2Resources
(
    SiteID int,
    ResourceID int,
    ResourceTypeID int
)

Table Journals
(
    JournalID int
)

What I'd like is for the Sites entity to have a "Journals" navigation property. The kicker is in the Sites2Resources table, Basically Sites2Resources.ResourceID=Journals.JournalID when Sites2Resources.ResourceTypeID=2.

Is this configuration possible through the LLBLGen designer? Alternatively, is there a way I can modify the generated code to put in this condition?

Thanks for any help! --Michael

Please try the following using the Designer, and repeat for each Resource Type.

On the Sites2Resources, create an Inheritance Hierarchy (Target Per Entity Hierarchy), using the ResourceTypeID as the discriminator field.

So you'd end up creating a subtype (entity) per each ResourceType....say: Sites2Journals, Sites2AnythingElse... etc.

Then from the designer you can define a model only relation from Sites2Journal to Journals.

Now Journals should have a navigator to Sites, as Sites has a relation to the SuperType of Sites2Resources and this should be inherited to the SubType Sites2Journals.

To have a Journals Navigator in the Sites, you will need to define a model only relation in the designer between Sites and Sites2Journals.

What Walaa said is correct, however Entity Framework doesn't support M:N relationships with non-hidden intermediate entities, which means SiteResources doesn't really exist for Entity Framework, they don't support an intermediate entity for m:n relationships.

Your relationship is what's called objectified and e.g. our framework supports it, but EF does not.

Awesome advice, I apparently have some reading to get this fully implemented correctly as I've not worked with Inheritance Hierarchies ever, but I was able to poke around in LLBLGen and get an example setup with the discriminator.

I'm a bit confused about what you said Otis. Basically from Walaa's advice, I'm setting up a new Entity Sites2Journals, and setting up Inheritance so that it is a subtype of Sites2Resources. Then I just create normal relationships to link together my three tables.

You mentioned a many-to-many relationship, which is what I originally thought I needed, but from Walaa's advice I don't see that I need to create a m:n relationship here, as I'll just navigate between Sites -> Sites2Journals -> Journals.

Thanks again, Michael

I didn't pay attention that you are using Entity Framework.

What Frans is saying is that since Site2Resources is an intermediate entity, and so will be the inherited types, e.g. Site2Journals. And correct you are creating 1:m relations on both sides of the intermediate entity. But the Navigator from Sites to Journals, means it detects the 1:m at both sides so it understands it's an m:n relation between Sites and Journals, through the intermediate entity.

This last part is not supported in EF, but it is supported in LLBLGen Framework, if you wish and have the luxury to switch frameworks.

Walaa wrote:

This last part is not supported in EF, but it is supported in LLBLGen Framework, if you wish and have the luxury to switch frameworks.

Got it, I think. So LLBLGen supports my Sites entity having a Journals navigation property. However Entity Framework will balk at this, so I'll have to setup navigation through Sites -> Sites2Journals -> Journals

Let me know if this assumption isn't correct!

It is correct.

Same question here in more details: http://llblgen.com/TinyForum/Messages.aspx?ThreadID=23609