Entity Framework Core 6 Many To Many Relationships with UsingEntity for additional data

English Forum
,
1

I have a fairly complex data structure with several Many to Many relations in Entity Framework Core 6. When using the UsingEntity to set up the structure for additional data for the relation, Entity Framework Core normally wants to have 2 collections in the object set up for the relationship. For example, if I have a lot of items with a serial number, I will want to be able to see what orders that serial number was sold to and an order may consist of several serial numbers. And there would be an object OrderSerialNumber that could keep track of how many of that serial number was shipped on a particular order.

OrderSerialNumber’s rows would then have two foreign keys, one for the order and one for the serial number, and a field to track qty.

So in the Order there would be two collections for this relationship based on the requirements of Entity Framework Core for Many to Many relationships. One collection of Serial numbers and one collection of OrderSerialNumbers.

And Serial numbers would have two similar relationships, one for the collection of orders and one for the OrderSerialNumbers.

The issue I am having is when I pass this data structure along to the Combit designer using the ObjectDataProvider, I get a crash with the following message.

combit.Reporting.LL_BadDatabaseStructure_Exception: The property ‘OrderSerialNumbers’ exists multiple times in the item hierarchy with different types. Make sure to name properties with different types differently.

I feel it’s because the OrderSerialNumbers is visible multiple times within the structure as you move down the hierarchy, because limiting the depth with the the creation of the provider can prevent the message, however, it drastically limits the amount of data that I can display on the report because the depth has to be limited so much. Is there a way to limit or hide certain fields from the view of the DataProvider? Or is there a way to adjust the names of the properties when they are discovered by the navigations?

Thanks for any assistance
Joe

2

Hey Joe,

seems interesting but the problem should me clear because of the named exception. Just for clarification:
In the tables/objects Orders AND SerialNumbers contains one property each called OrderSerialNumbers which is also the relation, right?

Maybe you can try to “rename” that properties into an individual name - directly in the model or you can just try to use the event AutoDefineField of the component of List & Label to identify and rename that properties to a more unique value.

Regards,
Oliver

3

This should be how the relationship is set up in the Configuration for EF Core, but both Orders and SerialNumbers has a single collection of OrderSerialNumbers. Orders also has a collection of SerialNumbers, while SerialNumbers has a collection of Orders.

builder.HasMany(o => o.Orders)
            .WithMany(sn => sn.SerialNumbers)
            .UsingEntity<OrderSerialNumber>
            (
                osn=> osn
                    .HasOne(o=>o.Order)
                    .WithMany(o=>o.OrderSerialNumbers)
                    .HasForeignKey(fk=>fk.OrderId),
                osn=> osn
                    .HasOne(sn=>sn.SerialNumber)
                    .WithMany(sn=>sn.OrderSerialNumbers)
                    .HasForeignKey(fk=>fk.SerialNumberId),
                j =>
                {
                    j.HasIndex(t => new { t.OrderId, t.SerialNumberId}).IsUnique();
                }
            );

I have tried renaming the collection in Orders to OrderSerialNumbers and leaving the one in SerialNumbers as SerialNumberOrders, but when that is done it seems to break EF Core navigations with Includes.

I tried using the AutoDefineField event once before by trying to exclude the column, but I must be doing something incorrectly. It didn’t seem to work the way I was trying it. I tried the method listed in the programming guide called “Suppress Data From a Data Source” and I passed the OrderSerialNumber name to try to exclude it, but it still threw the error, and the problem with that is that I sometimes need some data from that table.

Thanks for the ideas.

4

But that sounds a little bit strange, if you are renaming the mentioned collection consistently correct :thinking:

Not sure what you are exactly doing within the mentioned event AutoDefineField - excluding was not what I mean - just renaming by its Name property to the data is still there. Look at this pseudo-code:

...
void LL_AutoDefineField(object sender, AutoDefineElementEventArgs e)
{
	System.Diagnostics.Debug.WriteLine("original field name: " + e.Name);
	
	switch(e.Name)
	{
		case "Orders.OrderSerialNumbers":
		{
			e.Name = "Orders.OrderSerialNumbers";
		}
		break;
		
		case "SerialNumbers.OrderSerialNumbers":
		{
			e.Name = "SerialNumbers.SerialNumbersInOrders";
		}
		break;
		
		default:
		{
			// just leave
		}
		break;
	}
	
	System.Diagnostics.Debug.WriteLine("renamed field name: " + e.Name);
}
...

Does it work? What happens then?

Hint: You can also try to define the other AutoDefine[…] events to protocol all the names of tables, variables, fields and relations which are defined of the List & Label databinding for your data model.

Because of, the model itself is not clear for me in detail I would prefer to create a support case for combit to made some more analysis for that requirement. Maybe they have some more useful ideas.

Regards,
Oliver

5

Thanks for the suggestions.
So I tried adding the following to the initialization right after creating the new ListLabel object.

        Ll.AutoDefineField += LL_AutoDefineField;
        Ll.AutoDefineTable += LL_AutoDefineTable;
        Ll.AutoDefineTableRelation += LL_AutoDefineTableRelation;

To the initialization and then for simplicity I added the following to the object in question. I put breakpoints on each of the Writelines, however the crash seems to happen before hitting any of the breakpoints.

protected override void LL_AutoDefineField(object sender, AutoDefineElementEventArgs e)
    {
        System.Diagnostics.Debug.WriteLine("original field name: " + e.Name);
    }
    
    protected override void LL_AutoDefineTableRelation(object sender, AutoDefineDataItemEventArgs e)
    {
        System.Diagnostics.Debug.WriteLine("original table relation name: " + e.Name);
    }

    protected override void LL_AutoDefineTable(object sender, AutoDefineDataItemEventArgs e)
    {
        System.Diagnostics.Debug.WriteLine("original table name: " + e.Name);
    }

I may have to dig into the renaming of the collections in the code a bit more, to see if I can resolve that navigation issue to see if that solves my crash.

Thanks again.

6

If the events are not fired as expected, I think on the side of defining the data structure to List & Label you cannot engage further. Something earlier seems to be corrupted.

I would therefore suggest that you describe this to combit support and perhaps also provide a log file created with Debwin4.exe - ideally, you can even provide a small test application so that the professionals from combit can take a further look at it.

7

That’s what I was afraid of. Thanks for your help and guidance. I’ll keep tinkering with the renaming on the model side for now to see if I did something wrong. Otherwise, I’ll have to see what I can do about the support, since we are currently on a pay per minute support level.