Hi, using 4.2 Final (July 2015).

I understand the M:N relationships you can add via an intermediate entity as this: You have 2 1:M relationships where the M for both relationships is on the intermediate entity, you can then add an M:N between the 2 entities that are on the 1 ends.

Is it possible to add an M:N relations where the common entity is on the 1 end of both relationships . For example, you have 3 tables, order_items, products, and product_suppliers.

order_items M:1 products ( a product can be on many orders) product_suppliers M:1 products (a product can be supplied by many suppliers) Want to add a M:N between order_items and product_suppliers.

This doesn't seem possible as the "is related via" combo for order_items is empty presumably because it is not on the 1 end of any relationship.

Thanks,

order_items M:1 products ( a product can be on many orders) product_suppliers M:1 products (a product can be supplied by many suppliers) Want to add a M:N between order_items and product_suppliers.

Can you describe the semantic of this relation? So you have Orders, OrderItems, Products and Suppliers. Please note that ProductSuppliers is a m:n intermediate table between Products and Suppliers., So it is an artificial entity, you can't use it in your semantics. So what relation are you trying to get for the OrderDetails.

We guess you want the ternary relation between OrderItems, Products and Suppliers, i.e. which suppliers supplied which products for which Orders(OrderItems).

That's not a binary m:n relation.

Hi,

Your understanding is correct except that I want a relationship for "which suppliers are capable of fulfilling which order items" which is m:n.

so e.g.

CREATE TABLE order_item
(order_item_key int not null primary key identity(1,1),
product_key int not null foreign key references product(product_key)
)

CREATE TABLE supplier(
supplier_key int not null primary key identity (1,1)
)

CREATE TABLE product_supplier
(product_supplier_key int not null primary key identity(1,1),
product_key int not null foreign key references product(product_key)
supplier_key int not null foreign key references supplier(supplier_key)
)

SELECT * FROM order_item 
WHERE (CLAUSE1) -- returns a list of order_item each with a product_key

In SQL I could do something like

SELECT * FROM product_supplier
WHERE prioduct_key in (SELECT product_key FROM order_item
WHERE (CLAUSE1)

I can do that building a relation in code, but it would be nice to have as a relationship so I can do it using LINQ style queries with subpaths and prefetches.

Thanks

That's not available/supported. You will need to solve it programmatically, so either building the relation in code, or reside to database programmability (SP or TVF) which resultsets can be mapped to a TypedView.