Hi,

I would like to select all column of a join but I get an error.

I'm using LLBLGen 3.1, .NET 3.5 and SQL Server 2008 Express.

Here is the declaration of my tables.

Activity
{
      actCode   INT
      actTitle    NVARCHAR(130)
      ...
}

User
{
    uCode         ID
    uBirthday    DATETIME
    uFirstName  VARCHAR(200)
    uLastName   VARCHAR(200)
    ...
}

UserActivity
{
     uaUserCode      INT
     uaActivityCode  INT
     ...
}

So, it's simple, I have activities, users, and a table indicating who owns the activity. An activity can belong to several users.

From now, I want to get get all activities belonging to a specific user. And I need to returns all COLUMNS from two tablein the join. Here is the LINQ query I done :

DataAccessAdapter adapter = new DataAccessAdapter();
LinqMetaData lmd = new LinqMetaData(adapter);
var query = (from a in lmd.Activity
             join ua in lmd.UserActivity on a.ActCode equals ua.auActivityCode
             where ua.uaUserCode = 32
             select new 
             {
                a,
                ua
             }
            ).ToList();

But I always get the same error :

Unable to cast object of type 'System.Int32' to type 'xxx.EntityClasses.ActivityEntity'.

Do I have to list every field of all tables in my select or there is something I have not understood ?

Also, is it possible to add a predicate directly in the join condition ? Like this ?

DataAccessAdapter adapter = new DataAccessAdapter();
LinqMetaData lmd = new LinqMetaData(adapter);
Int32 iUserCode = 32;
var query = (from a in lmd.Activity
             join ia in lmd.UserActivite on new { a.ActCode, iUserCode } equals new { ua.uaActCode, ua.uaUserCode }
             select new 
             {
                a,
                ua
             }
            ).ToList();

Compiler don't like this one. I get the following error :

The type of one of the expressions in the join clause is incorrect.  Type inference failed in the call to 'Join'.

But I don't understand, I searched a bit on google, but I'm not sure I understood. I think with Linq, we can not specify that kind of condition in the join. It is right ?

Best regards, Martin

erakis wrote:

I'm using LLBLGen 3.1, .NET 3.5 and SQL Server 2008 Express.

Hi Martin. First of all, please make sure you are using the latest runtime library version. (http://llblgen.com/tinyforum/Messages.aspx?ThreadID=12769)

erakis wrote:

From now, I want to get get all activities belonging to a specific user. And I need to returns all COLUMNS from two tablein the join. Here is the LINQ query I done :

DataAccessAdapter adapter = new DataAccessAdapter();
LinqMetaData lmd = new LinqMetaData(adapter);
var query = (from a in lmd.Activity
             join ua in lmd.UserActivity on a.ActCode equals ua.auActivityCode
             where ua.uaUserCode = 32
             select new 
             {
                a,
                ua
             }
            ).ToList();

But I always get the same error :

Unable to cast object of type 'System.Int32' to type 'xxx.EntityClasses.ActivityEntity'.

Do I have to list every field of all tables in my select or there is something I have not understood ?

The query seems to be ok. Please provide more info: Generated SQL, exception message and stack trace.

Also, I think the query could be simplified:

LinqMetaData lmd = new LinqMetaData(adapter);
var query = (from ua in lmd.UserActivity
             where ua.uaUserCode = 32
             select new 
             {
                TheUserActivity = ua,
                TheActivity = ua.Activity
             }
            ).ToList();

erakis wrote:

Also, is it possible to add a predicate directly in the join condition ? Like this ? Compiler don't like this one. I get the following error :

The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'.

Please confirm you are using the latest runtime library version. I think this was fixed before. A workaround for that query is:

DataAccessAdapter adapter = new DataAccessAdapter();
LinqMetaData lmd = new LinqMetaData(adapter);
Int32 iUserCode = 32;
var query = (from a in lmd.Activity
             join ua in
                             (from userAct in lmd.UserActivity 
                              where userAct .uaUserCode == iUserCode
                              select userAct
                             )
                             on a.ActCode equals ua.uaActCode

             select new 
             {
                Activity = a,
                UserActivity = ua
             }
            ).ToList();

The both approaches you use return AnonymousTypes. You also could fetch UserActivity entity collection and prefetch its User and Activity related entities.

Thanks daelmo, it works now.

By the way, I love your second solution. I had not thought of