I have a summary table with tools and workers. So task with i can't handle is how to get count of lasts tools in the summary table that are not involved. I guess it would be like this

var res = context.ToolsSummaryTable.Include(t => t.Tool).Include(t => t.Worker)
          where(t.Tool.Name.Count() > t.Tool.Quantity);

Please Help!

Summary table Data

Tools table data

Workers table data

CodePudding user response：

You need a left outer join.
The left outer join is : "from tuUsedNone in g.DefaultIfEmpty()" See code below

using System;
using System.Linq;
using System.Text;
using System.Collections;
using System.Collections.Generic;

namespace ConsoleApp2
{
    class Program
    {
        static void Main(string[] args)
        {
            Context context = new Context()
            {
                Sumary = new List<Summary>()
               {
                   new Summary() {WorkerId = 1, ToolId = 2 },
                   new Summary() {WorkerId = 2, ToolId = 3 },
                   new Summary() {WorkerId = 3, ToolId = 2 },
                   new Summary() {WorkerId = 1, ToolId = 2 }
               },
                Tools = new List<Tools>()
               {
                   new Tools() { ToolId = 1, Name = "Screwdriver", Quantity = 2},
                   new Tools() { ToolId = 2, Name = "Hummer", Quantity = 3},
                   new Tools() { ToolId = 3, Name = "Wrench 17", Quantity = 1},
                   new Tools() { ToolId = 4, Name = "Pipe Wrench", Quantity = 2}
               },
                Workers = new List<Workers>()
               {
                   new Workers() { WorkerId = 1, FirstName = "Andrew", LastName = "Greekman"},
                   new Workers() { WorkerId = 2, FirstName = "Gordon", LastName = "Wingman"},
                   new Workers() { WorkerId = 3, FirstName = "Sam", LastName = "Peacekeeper"},
                   new Workers() { WorkerId = 4, FirstName = "Antony", LastName = "Scout"}
               }
            };
            var toolsUsed = context.Sumary.GroupBy(x => x.ToolId)
                .Select(x => new { toolId = x.Key, quantityUsed = x.Count() });
            var toolsRemaining = (from t in context.Tools 
                                  join tu in toolsUsed on t.ToolId equals tu.toolId into g
                                  from tuUsedNone in g.DefaultIfEmpty()
                                  select (new { Name = t.Name, toolsRemaining = (tuUsedNone == null) ? t.Quantity : t.Quantity - tuUsedNone.quantityUsed }))
                .ToList();
               
        }
 
    }
 
    public class Context
    {
        public List<Summary> Sumary { get; set; }
        public List<Tools> Tools { get; set; }
        public List<Workers> Workers { get; set; }
    }
    public class Summary
    {
        public int WorkerId { get; set; }
        public int ToolId { get; set; }
    }
    public class Tools
    {
        public int ToolId { get; set; }
        public string Name { get; set; }
        public int Quantity { get; set; }
    }
    public class Workers
    {
        public int WorkerId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }
}