Double many-to-many association issue

[vode]

Hello. I have tried linqer on set of use cases I have to use in my regular work. Simple cases look great but then I've tried to convert queries with many-to-many associations some issues were present. Here I explain the environment. I created 5 tables and made a chain A 1-* A_B *-1 B 1-* B_C *-1 C. There are 2 many-to-many associations in the chain. Then I have made a query


SELECT     A.AName, C.CName
FROM         A INNER JOIN
                     A_B ON A.AID = A_B.AID INNER JOIN
                     B ON A_B.BID = B.BID INNER JOIN
                     B_C ON B.BID = B_C.BID INNER JOIN
                     C ON B_C.CID = C.CID
WHERE     (C.CName = 'qqq')


After conversion I got the wrong result:

from t0 in db.A_B
join t123 in db.B_C on t0.BID equals t123.C.B.BID
where
 t123.C.CName == "qqq"
select new {
 t0.A.AName,
 t123.C.CName
}


Error Compiling Expression: Error Compiling Expression: 'abc.C' does not contain a definition for 'B' and no extension method 'B' accepting a first argument of type 'abc.C' could be found (are you missing a using directive or an assembly reference?)

The result code contains the statement t123.C.B.BID instead of t123.B.BID

After this I have tried another variant of query (unnecessary for me join to B is excluded)


SELECT     A.AName, C.CName
FROM         C INNER JOIN
                     B_C ON C.CID = B_C.CID INNER JOIN
                     A INNER JOIN
                     A_B ON A.AID = A_B.AID ON B_C.BID = A_B.BID
WHERE     (C.CName = 'qqq')


This query was not convertible at all:

SQL cannot be converted to LINQ: Field [A_B.AID ON B_C].[BID = A_B.BID] not found in the current Data Context.

At the end of this post I attached the database creation SQL script for database I have linqer tested with.


CREATE TABLE [A](
[AID] [int] IDENTITY(1,1) NOT NULL,
[AName] [varchar](50) NULL,
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[AID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [B](
[BID] [int] IDENTITY(1,1) NOT NULL,
[BName] [varchar](50) NULL,
CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED
(
[BID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [C](
[CID] [int] IDENTITY(1,1) NOT NULL,
[CName] [varchar](50) NULL,
CONSTRAINT [PK_C] PRIMARY KEY CLUSTERED
(
[CID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [A_B](
[AID] [int] NOT NULL,
[BID] [int] NOT NULL,
CONSTRAINT [PK_A_B] PRIMARY KEY CLUSTERED
(
[AID] ASC,
[BID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [B_C](
[BID] [int] NOT NULL,
[CID] [int] NOT NULL,
CONSTRAINT [PK_B_C] PRIMARY KEY CLUSTERED
(
[BID] ASC,
[CID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [A_B]  WITH CHECK ADD  CONSTRAINT [FK_A_B_A] FOREIGN KEY([AID])
REFERENCES [A] ([AID])
GO
ALTER TABLE [A_B] CHECK CONSTRAINT [FK_A_B_A]
GO
ALTER TABLE [A_B]  WITH CHECK ADD  CONSTRAINT [FK_A_B_B] FOREIGN KEY([BID])
REFERENCES [B] ([BID])
GO
ALTER TABLE [A_B] CHECK CONSTRAINT [FK_A_B_B]
GO
ALTER TABLE [B_C]  WITH CHECK ADD  CONSTRAINT [FK_B_C_B] FOREIGN KEY([BID])
REFERENCES [B] ([BID])
GO
ALTER TABLE [B_C] CHECK CONSTRAINT [FK_B_C_B]
GO
ALTER TABLE [B_C]  WITH CHECK ADD  CONSTRAINT [FK_B_C_C] FOREIGN KEY([CID])
REFERENCES [C] ([CID])
GO
ALTER TABLE [B_C] CHECK CONSTRAINT [FK_B_C_C]
GO


I hope these issues will be fixed in the future releases of such helpful tool as linqer is.

[Mikhail Oumantsev]

Can you send your dbml and designer.cs files?
That would speed up my investigation.
I suspect your dbml file is broken.
My email is [e-mail]

[vode]

dbml file and designer.cs file was generated using generation utility on the connection configuration page of linqer. Steps were next:

1. I have made a connection to SQL Server to the database was made from the script I had attached in my first message. (Data Source=XXX\SQL2008;Initial Catalog=LinqerTest;Integrated Security=True)

2. Selected LINQ to SQL as a Model

3. Pressed the button "Generate LINQ to SQL Files" then selected a target folder and as result I had a files you ask me for.

So. The test was clean without third party tools involved. Except SqlMetal.exe you use to generate the subj internally

[Mikhail Oumantsev]

Ok.
I can reproduce this error.
As a workaround you can turn off "Use Foreign Keys to simplify LINQ" flag. This flag can be found under Tools->Options menu.
I am preparing a maintenance update for Linqer, so this fix will be included.

Thanks.

[vode]

Yes. The problem is in using associations generated in entity classes by SqlMetal to reduce amount of explicit joins. I have found one more issue with it. It is in case there are two or more fields in the association and I use join only by one of these field in the query. Converter puts whole association instead of using explicit join by one field only. This bug is simple to reproduce so I do not attach any details here. Good luck.