Inner Join on three tables using Linq

Question

How to do inner join on three tables, when one of the tables is using group by. I am able to write this query but it returns error on run time. So how should I modify my query.

var pastDate = DateTime.Now.Date.AddDays(-1);

var query = from l in db.picturelikes
            where l.iddatetime > pastDate
            group l by l.idpictures into pgroup
            let count = pgroup.Count()
            orderby count descending
            join p in db.picturedetails on pgroup.FirstOrDefault().idpictures equals p.idpictures
            join u in db.users on pgroup.FirstOrDefault().iduser equals u.iduser
            select new SortedDto
            {
                IdPictures = pgroup.FirstOrDefault().idpictures,
                IdUser = pgroup.FirstOrDefault().iduser,
                totalrating = pgroup.Average(l => (float?)l.likenumber) ?? 0, // pl.Where(a => a.likenumber!= null).Average(c => c.likenumber)
                sex =u.sex,
                username =u.username,
                dob = u.dob 
            };

return query;

This is the exception i get with my present code,

InnerException: { Message: "An error has occurred.", ExceptionMessage: "Unknown column 'Project2.idpictures' in 'where clause'", ExceptionType: "MySql.Data.MySqlClient.MySqlException", StackTrace: " at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.Entity.EFMySqlCommand.ExecuteDbDatoiaReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)" }

Is my query legit? Is it the way to do three inner joins when using groupby.

Try to use pgroup.Key instead of pgroup.FirstOrDefault().idpictures — Sergey Berezovskiy
pgroup.key gives me access to just key of the like not to idpictures and others. — Obvious
@Hmmmmmmmmmm: Why is this a join on three tables? p in db.picturedetails is never used. — Andrew Coonce
Yeah but later on i need to use table three too i.e db.picturedetails — Obvious

Servy Servy · Accepted Answer · 2013-09-30T15:13:42

The query provider can't handle complex actions for determining the identity of each set for the "join".

Fortunately, you don't need to do something as complex as you are doing. Rather than using FirstOrDefault in the join comparision to get at the field you're grouping on, just use the groups's key:

join p in db.picturedetails on pgroup.Key equals p.idpictures

You can, and should use Key elsewhere in the query as well.

If you want to make it clearer to the reader what they key is, and so want a better variable name, just use a let:

let detailsId = pgroup.Key

And then you can use detailsId throughout the query instead.

Since your group needs to also have a constant userId value you should also be grouping on that, assuming the groups all currently have the same userId value:

group l by new{ l.idpictures, l.iduser } into pgroup

Then you can access the property of the key that you need in the respective join clauses:

join p in db.picturedetails on pgroup.Key.idpictures equals p.idpictures

join u in db.users on pgroup.Key.iduser equals u.iduser

Inner Join on three tables using Linq

2 Answers