Joining Tables Using Entity Framerwork Fluent Syntax

Question

I have tables Table1, Table2, Table3 and so on. To handle auditing, I use Audit table with the following fields Id, Username, ChangeTime, ChangeType, TableName and TableRecordId.

To obtain all records in Table1 joined with Audit on Table1.Id = Audit.TableRecordId where Username is "jdoe" for example, I have the following:

var results = db.Table1s
            .Join(db.Audits.Where(a => a.Username == "jdoe"),
            t => t.Id,
            a => a.RecordId,
            (t, a) => new { Table1 = t, Audit = a });

But when I run it I get the following error:

The model item passed into the dictionary is of type 'System.Collections.Generic.List`1[<>f__AnonymousType1`2[SampleApp.Models.Table1,AuditLibraryContext.Models.Audit]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[AuditTrackerSample.Models.Table1]'.

Got it working I ended up with the following code:

var results = db.Table1s
            .Join(db.Audits.Where(a => a.Username == "jdoe"),
            t => t.Id,
            a => a.RecordId,
            (t, a) => t);

What do your entities look like? Using navigation properties, you shouldn't have to join since EF does it for you. — Neil Smith, May 16 '14 at 19:08
Can't use navigation because I don't have direct access to the Audit class which is part of a third party library. — corix010, May 16 '14 at 20:46

score 6 · Accepted Answer · edited May 23 '17 at 12:15

6

Please do a thorough search before asking a general duplicate question.

var results = db.Table1s
              .Join(db.Audits.Where(a => a.Username == "jdoe"),
              t => t.Id,
              a => a.TableRecordId,
              (t, a) => new { Table1 = t, Audit = a });

var results = from t in db.Table1s
              join a in db.Audits
              on t.Id equals a.TableRecordId
              where a.Username == "jdoe"
              select new { Table1 = t, Audit = a};

Reference

edited May 23 '17 at 12:15

Community

1
1

answered May 16 '14 at 19:31

user1417835

1,002
1
8
14

1

Thanks. I did some research before asking and I did find some stuff but not exactly what I am looking for and I am having a hard time figuring out exactly how to apply them in my situation. – corix010 May 16 '14 at 20:55
Oh and Username is part of the Audit table not Table1. – corix010 May 16 '14 at 20:56
Where clause has been fixed, as per your clarification. – user1417835 May 16 '14 at 21:08
Thanks. Will try this out as soon as I get the chance. – corix010 May 16 '14 at 21:25
2

Please close-vote a question as duplicate when finding a duplicate. – Gert Arnold May 16 '14 at 21:41
It does not appear that anyone, other than yourself, that have responded, have sufficient reputation to cast a close vote. :( – user1417835 May 16 '14 at 21:49
I tried the above fluid syntax but I am getting the following error: The model item passed into the dictionary is of type 'System.Collections.Generic.List`1[<>f__AnonymousType1`2[AuditSample.Models.Table1,AuditLibraryContext.Models.Audit]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[AuditSample.Models.Table1]'. – corix010 May 20 '14 at 14:36

Joining Tables Using Entity Framerwork Fluent Syntax

1 Answers1