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, Sep 30 '13 at 14:11
pgroup.key gives me access to just key of the like not to idpictures and others. — Obvious, Sep 30 '13 at 14:12
@Hmmmmmmmmmm: Why is this a join on three tables? `p in db.picturedetails` is never used. — Andrew Coonce, Sep 30 '13 at 14:32
Yeah but later on i need to use table three too i.e db.picturedetails — Obvious, Sep 30 '13 at 14:34
To add to the question , even if i dont use where clause i still get the same error. — Obvious, Sep 30 '13 at 14:35

Servy · Accepted Answer · 2013-09-30T15:39:06.877

2

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

edited Sep 30 '13 at 15:39

answered Sep 30 '13 at 15:13

Servy

202,030
26
332
449

This wont work. pgroup.key will be using idlike to join on p.idpictures from db.picturedetails. Which is not what i want. I want to join both using idpictures. – Obvious Sep 30 '13 at 15:22
@Hmmmmmmmmmm That's the field that you grouped on, so the value of that field is what the `Key` will contain. – Servy Sep 30 '13 at 15:25
You mean to say key will be containing idpicture? – Obvious Sep 30 '13 at 15:27
Yeah you are right, its working . But how to join the user table? – Obvious Sep 30 '13 at 15:29
join u in db.users on pgroup.Key equals u.iduser into second this wont join – Obvious Sep 30 '13 at 15:29
Well, the items in that group won't have the same idUser value, so the query inherently won't mean much. You should group on that as well if you need to access that property. – Servy Sep 30 '13 at 15:29
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/38350/discussion-between-hmmmmmmmmmm-and-servy) – Obvious Sep 30 '13 at 15:30
i do have iduser property in pictures table, any way to join ?? – Obvious Sep 30 '13 at 15:35

score 0 · Answer 2 · answered Sep 30 '13 at 14:41

0

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.

answered Sep 30 '13 at 14:41

Robert McKee

21,305
1
43
57

new { l.idpictures,l.idgroup } , i didnt get it? what is l.idgroup – Obvious Sep 30 '13 at 14:44
How can i join first and then group, because i need to my likes to be grouped and become one single like so that they can be joined. Can you clarify? – Obvious Sep 30 '13 at 14:51
Sorry, I meant new {id.idpictures,l.iduser} – Robert McKee Oct 01 '13 at 15:14

Inner Join on three tables using Linq

2 Answers2