CODEWITHSUNDEEP
c#mysqlasp.net-mvclinqlambda
Obvious
Obvious

Reputation: 344

Inner Join on three tables using Linq

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.

Upvotes: 2

Views: 5680

Answers (2)

Servy
Servy

Reputation: 203802

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

Upvotes: 2

Robert McKee
Robert McKee

Reputation: 21487

Try changing:

group l by l.idpictures into pgroup

to:

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

you will likely have to change your references of pgroup.FirstOrDefault().idpictures to pgroup.idpictures

alternatively, rework your group so you join first, then group.

Upvotes: 0

Related Questions