Hi, I am trying to do a join and get a SqlException Invalid column Name error. Am I doing something wrong (if so do you have an example)?
In a mockup I have 2 tables Pet and Person. Pet has FK to Person. The code I have written 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);
}
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'
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 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at ...()
at ..(IUnitOfWork , IDbCommand , )
at ..(IUnitOfWork , IDbCommand )
at ...ctor(IUnitOfWork , IEnumerable`1 )
at Mindscape.LightSpeed.Data.DataProviderAdapter.(IUnitOfWork , IEnumerable`1 , ProviderOptions )
at ..(TypeModel , Query , IList )
at ..(UnitOfWorkBase , TypeModel , Query , IList )
at ..(Query , TypeModel , IList )
at ..(Query , IList )
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: