Show combined result from unrelated tables (linq to sql)

Question

I'm struggling to work out the best way to show a combined result from two unrelated tables using linq to sql (C#). The tables are EmailAlerts and TextAlerts, each with UserName, Type and Status (plus other columns that differ, but only need these three for this query). For reporting purposes, I need to get a snapshot of users who have an active alert in the system.

Example tables:

EmailAlerts
UserName    Type    Status
Alice       1   0
Alice       1   0
Alice       1   1
Alice       2   0
Alice       2   1
Alice       2   1
Bob         1   1
Bob         2   1
Mallory     1   1
Mallory     2   1

TextAlerts
UserName    Type    Status
Alice       1   1
Alice       2   0
Alice       2   1
Bob         1   0
Mallory     1   1
Mallory     2   1

This will be put in a csv file, and the final result for the example tables should look like this:

Username, ActiveType1Email, ActiveType2Email, ActiveType1Text, ActiveType2Text
Alice, Yes, Yes, No, Yes
Bob, No, No, Yes, No

So, for each unique user, find out if they have an active (status = 0) email or text alert of either type. They can have multiple alerts for both types. The users are stored in Sitecore so there is no user table.

At the moment I'm first getting all the unique usernames, then looping through each one to find out what alerts they have. It works, but it's pretty horrible so I'd like to find a better solution. Is it possible to do it all in one query? Would a stored procedure be a better way of doing it? If anyone can point me in the right direction I can try to figure out the code myself, I'm just not sure of best way to tackle it.

UPDATE: Here's the current (ugly) code:

public static List<Dictionary<string, string>> GetUserAlertsForCSV()
{
    List<Dictionary<string, string>> alerts = new List<Dictionary<string, string>>();

    var usernames = ((from e in db.EmailAlerts select e.UserName).Union
                    (from t in db.TextAlerts select t.UserName)).Distinct();

    foreach (var username in usernames)
    {
        Dictionary<string, string> d = new Dictionary<string, string>();
        d.Add("username", username);
        bool hasActiveAlert = false;

        var activeType1Email = (from e in db.EmailAlerts
                    join a in db.AlertStatusCodes on e.Status equals a.StatusCode
                    where e.UserName == username
                    && e.Type == (int)AlertType.Type1
                    && a.Description == "active"
                    select e).FirstOrDefault();

        if (activeType1Email != null)
        {
            d.Add("type1email", "Yes");
            hasActiveAlert = true;
        }
        else
        {
            d.Add("type1email", "No");
        }

        // repeat the above for activeType1Text, activeType2Email and activeType2Text

        if (hasActiveAlert)
        {
            alerts.Add(d);
        }
    }

    return alerts;
}

Thanks,

Annelie

Could you show us the code of what you are trying to achieve, maybe it will give us a hint? This way the goal is kind of unclear. — TheBoyan, Mar 08 '11 at 19:31
@bojanskr - Added example tables, will also add the linq to sql code I have so far, but be warned, it smells! :) — annelie, Mar 08 '11 at 19:39
I've corrected the solution, check out the edited answer below — TheBoyan, Mar 09 '11 at 11:53

TheBoyan · Accepted Answer · 2011-03-09T12:00:36.443

Try this out, if anything it might give you an idea. My idea behind it is to use correlated subqueries a new anonymous type to store all the information you need. Check it out:

var usernames = ((from e in db.EmailAlerts select e.UserName).Union
                (from t in db.TextAlerts select t.UserName)).Distinct();
var result =
    from u in usernames
    select new 
    {
        Username = u.Username,
        ActiveType1Email = (from e in db.EmailAlerts
                           where e.UserName == u
                           && e.Type == (int)AlertType.Type1
                           && a.Description == "active"
                           select e).FirstOrDefault();
        /*
         ... and so on repeat for activeType1Text, activeType2Email and activeType2Text

        */
    }

    // and then go trough the result set which is IEnumarable<T> and use it for what you need
    foreach(var a in result)
    { 
      var something = a.ActiveType1Email;
      /* etc. */ 
    }

Does this provide any help to you?

Sorry, I was being unclear, will update my question with example tables. The user doesn't have to have a record in both tables. — annelie, Mar 08 '11 at 19:30
This looks nicer, I've been trying to make it into one or two big queries but this might be the best way to tackle it. Thanks for you help! — annelie, Mar 10 '11 at 15:46

Show combined result from unrelated tables (linq to sql)

1 Answers1