markrendle/Simple.Data — Issue #118: Joins in Simple.Data fails with "The multi-part identifier "dbo.Categories.Name" could not be bound."
Author: marcushammarberg
Created: 2011-11-20 19:01:25+00:00
State: open

I run this join and get a Simple.Data.Ado.AdoAdapterException : The multi-part identifier "dbo.Categories.Name" could not be bound. (from a System.Data.SqlClient.SqlException : The multi-part identifier "dbo.Categories.Name" could not be bound.)

```c#
result = db.Items.FindAll(db.Items.UserId == userId &&
db.Items.Categories.Name == category)
.Cast();
```

Here's my tables (and yes there's a PK on each table called Id)

```SQL
CREATE TABLE [dbo].[Users](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Categories](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NOT NULL,
[UserId] [int] NOT NULL,
) ON [PRIMARY]

ALTER TABLE [dbo].[Categories] WITH CHECK ADD CONSTRAINT [FK_Categories_Users] FOREIGN KEY([UserId])
REFERENCES [dbo].[Users] ([Id])
GO

CREATE TABLE [dbo].[Items](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ItemType] [varchar](40) NOT NULL,
[Data] [text] NOT NULL,
[Created] [datetime] NOT NULL,
[Read] [bit] NOT NULL,
[Public] [bit] NOT NULL,
[Notes] [text] NULL,
[UserId] [int] NOT NULL,
[CategoryId] [int] NOT NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Items] WITH CHECK ADD CONSTRAINT [FK_Items_Categories] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[Categories] ([Id])
GO

ALTER TABLE [dbo].[Items] WITH CHECK ADD CONSTRAINT [FK_Items_Users] FOREIGN KEY([UserId])
REFERENCES [dbo].[Users] ([Id])
GO
```

More on this issue can be found in this gist and comments: https://gist.github.com/1379343