Joining results from two queries in C# using LINQ or other module

Question

I have two queries taking data from a single table with lambda and I would like to use something like a SQL 'join on' method in order to join the tables on a single id. The queries are:

    var tbl_emails_received =
        db.EmailAddresses
        .GroupBy(x => new { x.RecepientID })
        .Select(x => new
        {
            x.Key.RecepientID,
            EmailsRecieved = x.Count()

        });

and

        var tbl_emails_sent =
        db.EmailAddresses
        .GroupBy(x => new { x.SenderID })
        .Select(x => new
        {
            x.Key.SenderID,
            EmailsSent = x.Count()
        });

I am trying to replicate what this MsSQL Query is doing. I tried looking at Microsoft's documentation for join (linked here) but it requires new classes to hold the objects.

WITH 
tbl_emails_received AS
    (
        SELECT RecepientID, count(FileID) AS EmailsRecieved 
        FROM tbl_ex_EmailAddresses
        GROUP BY RecepientID
    ),
tbl_emails_sent AS
    (
        SELECT SenderId, count(FileID) AS EmailsSent
        FROM tbl_ex_EmailAddresses
        GROUP BY SenderID
    )

SELECT s.SenderID as Id, r.EmailsRecieved, s.EmailsSent, 
    r.EmailsRecieved + s.EmailsSent as TotalEmails,
    slist.EmailAddress, slist.EmailName, slist.DomainName
FROM tbl_emails_received r
    JOIN tbl_emails_sent s
        ON r.RecepientID = s.SenderID

But it could be that LINQ or C# cannot create objects on the fly like with SQL does with the data. My question is, is there a way I can replicate what the SQL query is doing in LINQ or simple C#? Are there any best practices for this kind of queries? Is there another package I could use that could potentially make this simpler?

This probably help. https://stackoverflow.com/questions/2767709/join-where-with-linq-and-lambda — Juan Carlos Oropeza, Jun 18 '19 at 15:55
As the two LINQ queries are not executed at this stage and are using anonymous types then join them into a type that returns your required results. — Peter Smith, Jun 18 '19 at 16:08
I wonder if your data model is correct. I don't understand what an `EmailAddress` needs a sender and a recipient id for. I feel that a better data model would make it easier to get the information you need. If you're in for improvement in this area consider posting a new question, *maybe* on [Code Review](https://codereview.stackexchange.com/help/on-topic). — Gert Arnold, Jun 18 '19 at 19:51
@GertArnold I agree. The dataModel is a bit misleading but it has been used for a while so it's hard to fix it without making large scale changes in the Database and the .NET Project. The EmailAddressID actually represents one email sent from a Sender to a Receiver. — swopnil, Jun 18 '19 at 20:35

Peter Smith · Accepted Answer · 2019-06-18T17:33:21.757

You could try something like this (using your two initial queries)

var query = (from er in tbl_emails_received 
            join es in tbl_emails_sent on er.RecepientID equals es.SenderID
            select new emailsReceieved = er, emailsSent = es
            ).ToList();
EMailClass finalClass = query.select(
            x => new EMailClass
            {
            Id = x.emailsSent.SenderID,
            EmailsReceived = x.emailsReceieved.EmailsRecieved
            ...
            }).ToList();

This has not been tested or fully checked. The only trip to the database is the creation of query. There is only one class which is the one that is equivalent to the output from your SQL SELECT statement. (And it has to go somewhere). Or, you could also make that an anonymous class.

score 1 · Answer 2 · answered Jun 18 '19 at 16:35

1

run select query on db.EmailAddresses .ToList() to get all records from table, now run group by for recipientId , SenderId on top of this query. This way database hit is once only

answered Jun 18 '19 at 16:35

dotnetavalanche

804
2
12
25

Joining results from two queries in C# using LINQ or other module

2 Answers2