1

2 tables: User and Alarm

Table:User UserID(int), FullName(varchar)

Table:Alarm AssignedTo(int), Resolved(bool)

Query:

SELECT u.Fullname, COUNT(resolved) as Assigned, SUM(CONVERT(int,Resolved)) as Resolved, COUNT(resolved) -  SUM(CONVERT(int,Resolved)) as Unresolved
FROM Alarm i LEFT OUTER JOIN Users u on i.AssignedTo = u.UserID
GROUP BY u.Fullname

Results:

Fullname  Assigned  Resolved  Unresolved
User1     204       4         200
User2     39        9         30
User3     235       200       35
User4     1         0         1
User5     469       69        400

For the life of me I can't figure out how to make this into a Linq query. I am having trouble with the grouping function. I've looked a countless examples and none have my combination of Left Outer join with grouping or they are so complicated that I can't figure out how to make it work with mine. Any help here would be Greatly appreciated!!!

Update: I may not have been clear in what I'm looking for. I am looking for the alarms grouped by the AssignedTo Column which is a userid... Except, I want to replace that userid with the FullName that is located in the users table. Someone had posted and deleted something close except it gave me all users in the user table which is not what I'm looking for..

Update 2: See my answer below

Wizardre
  • 33
  • 7
  • Does the Alarm table have a primary key? – Yacoub Massad Oct 09 '15 at 21:10
  • 1
    You know that your WHERE clause turns your LEFT JOIN into an INNER JOIN – juergen d Oct 09 '15 at 21:11
  • yes, it's "id" but not needed in the sql query as we are joining on the AssignedTo field in the alarm table and the UserId field in the User table. – Wizardre Oct 09 '15 at 21:12
  • Here's another [example](https://msdn.microsoft.com/en-us/vstudio/ee908647.aspx#leftouterjoin) from MSDN. Are you getting the wrong results or an error message? – Morpheus Oct 09 '15 at 21:12
  • Wow! Didn't even realize that Juergen! But, this does give me the results I need. So how do I do this in a linq query? :) – Wizardre Oct 09 '15 at 21:13
  • Morpheus I'm getting the right results for the SQL query but I need this in a linq query. – Wizardre Oct 09 '15 at 21:14
  • Possible duplicate of [LEFT OUTER JOIN in LINQ](http://stackoverflow.com/questions/3404975/left-outer-join-in-linq) – Preston Guillot Oct 09 '15 at 21:47
  • @PrestonGuillot That link contains examples of Left Outer Joins. It says nothing about grouping. But thanks, it was an interesting read. – Wizardre Oct 09 '15 at 23:49

4 Answers4

2

Assuming that you have the following models:

This is the model for Alarm:

public class Alarm
{
    public int id { get; set; }

    public int AssignedTo { get; set; }

    [ForeignKey("AssignedTo")] 
    public virtual User User { get; set; }

    public bool Resolved { get; set; }
}

This is the model for User:

public class User
{
    public int UserID { get; set; }

    public string FullName { get; set; }

    public virtual ICollection<Alarm> Alarms { get; set; }

    public User()
    {
        Alarms = new HashSet<Alarm>();
    }
}

This is the model that will hold the alarm statistics for each user:

public class UserStatistics
{
    public string FullName { get; set; }
    public int Assigned { get; set; }    
    public int Resolved { get; set; }    
    public int Unresolved { get; set; }    
}

You can then do the following:

var query = context.Users.Select(
    user =>
        new UserStatistics
        {
            FullName = user.FullName,
            Assigned = user.Alarms.Count,
            Resolved = user.Alarms.Count(alarm => alarm.Resolved),
            Unresolved = user.Alarms.Count(alarm => !alarm.Resolved)
        });


var result = query.ToList();

By the way, you can also modify the query and remove Unresolved = user.Alarms.Count(alarm => !alarm.Resolved), and then make the Unresolved property a calculated property like this:

public class UserStatistics
{
    public string FullName { get; set; }
    public int Assigned { get; set; }    
    public int Resolved { get; set; }    
    public int Unresolved
    {
        get { return Assigned - Resolved; }
    }
}

This will make the generated SQL query simpler.

Yacoub Massad
  • 27,509
  • 2
  • 36
  • 62
  • While this Extremely impressive, there is so much here that I don't understand :) My classes are definitely not set up like that. Maybe they should be but I need to understand a few things like Virtual Icollections and where the User() function is getting called from. You get an A for effort!!! :) – Wizardre Oct 09 '15 at 23:45
  • What is the data access technology that you are using? (e.g. Entity Framework, LINQ to SQL...). What does your model look like? Did you write it with code? Or did you have it auto-generated from the database? – Yacoub Massad Oct 10 '15 at 15:15
  • Massd - I'm using Entity Framework. For all intents and purposes my model is as described above but with a lot more properties in each table. I have figure it out and posted my answer in lambda format. How would I write this in linq sql-like syntax? Thanks! – Wizardre Oct 11 '15 at 01:41
1

I finally figured it out.

This:

var results = alarms.GroupBy(x => x.AssignedTo)
.Join(users, alm => alm.Key , usr => usr.UserID, (alm, usr) => new {
    Fullname = usr.FullName,AssignedNum = alm.Count(),
    Resolved = alm.Where(t=>t.resolved == true).Select(y => y.resolved).Count(), 
    Unresolved = alm.Where(t=>t.resolved == false).Select(y => y.resolved).Count() });

Reproduces This: 

SELECT u.Fullname, COUNT(resolved) as Assigned, SUM(CONVERT(int,Resolved)) as Resolved, 
       COUNT(resolved) -  SUM(CONVERT(int,Resolved)) as Unresolved
FROM Alarm i LEFT OUTER JOIN Users u on i.AssignedTo = u.UserID
GROUP BY u.Fullname

The result is grouped by the AssignedTo (int) but AssignedTo is not selected. Instead FullName is selected from the joined user table.

Many thanks to everyone that tried to help! I learned a lot from your answers.

For bonus points, how would I write my lamdbda answer in a SQL like syntax?

Wizardre
  • 33
  • 7
0

Try this :

from u in context.User
join a in context.Alarm on u.UserID equals a.AssignedTo into g1
from g2 in g1.DefaultIfEmpty()
group g2 by u.Fullname into grouped
select new { Fullname = grouped.Key, Assigned = grouped.Count(t=>t.Resolved != null), Resolved = grouped.Sum
                                    (t => int.Parse(t.Resolved)), Unresolved = (grouped.Count(t=>t.Resolved != null) - grouped.Sum
                                    (t => int.Parse(t.Resolved)))}
T. Sebastian
  • 1
  • 2
  • This is similar what I tried to do but I got the same errors from mine that I'm getting from this on the **'group g2 by u.Fullname into grouped'** line. **"Cannot convert lambda expression to type 'System.Collections.Generic.IEqualityComparer' because it is not a delegate type"** and this **"'MyProject.Models.User' does not contain a definition for 'Fullname' and no extension method 'Fullname' accepting a first argument of type 'MyProject.Models.User' could be found (are you missing a using directive or an assembly reference?)"** – Wizardre Oct 09 '15 at 23:12
  • Extract any C# calls (such as Int32.Parse) out of the query and pass them as constant values. If you can't - it's impossible to convert your LINQ query to SQL. So you need reconsider it (or do this in memory what is a big no-non until you really know what you're doing). – abatishchev Oct 10 '15 at 01:40
0

I guess it is not necessarily to use "Grouping" for this query in Linq because the combination of "LEFT JOIN" + "GROUP BY" changed them over to "INNER JOIN".

    var results =
        from u in users
        join a in alarms on u.UserID equals a.AssignedTo into ua
        select new
        {
            Fullname = u.FullName,
            Assigned = ua.Count(),
            Resolved = ua.Count(a => a.Resolved),
            Unresolved = ua.Count(a => !a.Resolved)
        };

        foreach (var r in results)
        {
            Console.WriteLine(r.Fullname + ", " + r.Assigned + ", " + r.Resolved + ", " + r.Unresolved);
        }
jhmt
  • 1,401
  • 1
  • 11
  • 15