Hi, I am doing a Join and getting a SQLException Invalid Column Name. (I can see from the generated SQL that it is not using the correct column name in the "On" part of the "Join"). Do you have a similar example which generates the correct SQL ?
In a mockup I have 2 tables: Person, Pet. Pet has FK to Person
The Code is:
public void Get(string criteriaPetName)
{
LightSpeedContext<TestLSUnitOfWork> context = new LightSpeedContext<TestLSUnitOfWork>();
context.DataProvider = DataProvider.SqlServer2008;
context.ConnectionString = "Data Source=localhost;Initial Catalog=TestLS;Integrated Security=true";
TestLSUnitOfWork work = context.CreateUnitOfWork();
Query subquery = new Query(typeof(Pet));
subquery.QueryExpression = Entity.Attribute("NickName") == criteriaPetName;
Query query = new Query(typeof(Person));
query.Join = Join.Inner(typeof(Person), subquery, "Id", "PersonId");
var joinedResults = work.Find<Person>(query);
}
The SQL Generated Is:
exec sp_executesql N'SELECT
t0.Id AS [t0.Id],
t0.CreatedOn AS [t0.CreatedOn],
t0.DeletedOn AS [t0.DeletedOn],
t0.LastName AS [t0.LastName],
t0.LockVersion AS [t0.LockVersion],
t0.UpdatedOn AS [t0.UpdatedOn]
FROM
Person t0
INNER JOIN
(SELECT
Pet.Id AS [Pet.Id],
Pet.CreatedOn AS [Pet.CreatedOn],
Pet.DeletedOn AS [Pet.DeletedOn],
Pet.LockVersion AS [Pet.LockVersion],
Pet.NickName AS [Pet.NickName],
Pet.PersonId AS [Pet.PersonId],
Pet.UpdatedOn AS [Pet.UpdatedOn]
FROM
Pet
WHERE
(Pet.NickName = @p0 AND Pet.DeletedOn IS NULL)
) q1
ON
t0.Id = q1.PersonId
WHERE
t0.DeletedOn IS NULL',N'@p0 nvarchar(5)',@p0=N'Lucky'
The Exception is:
System.Data.SqlClient.SqlException was unhandled by user code
Message=Invalid column name 'PersonId'.
Source=.Net SqlClient Data Provider
ErrorCode=-2146232060
Class=16
LineNumber=25
Number=207
Procedure=""
Server=localhost
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
...
at Mindscape.LightSpeed.UnitOfWork.Find(Query query, IList results)
at Mindscape.LightSpeed.UnitOfWorkBase.Find[TEntity](Query query)
at ClassLibrary1.TestJoin.Get(String criteriaPetName) in c:\users\simon.dowdeswell\documents\visual studio 2010\Projects\TestProject1\ClassLibrary1\TestJoin.cs:line 27
at TestProject1.UnitTest1.TestMethod1() in c:\users\simon.dowdeswell\documents\visual studio 2010\Projects\TestProject1\TestProject1\UnitTest1.cs:line 17
InnerException: