CODEWITHSUNDEEP
c#sql-serverlinqasp.net-web-api
swopnil
swopnil

Reputation: 367

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

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?

Upvotes: 0

Views: 1798

Answers (2)

Peter Smith
Peter Smith

Reputation: 5550

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.

Upvotes: 1

dotnetavalanche
dotnetavalanche

Reputation: 890

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

Upvotes: 1

Related Questions