如何获得工具表中持续的名称的收集c＃linq

Question

我有一个带有工具和工人的摘要表。因此，我无法处理的任务是如何在不涉及的摘要表中获取最后的工具计数。我想这就是这样，

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

请帮忙！

数据

工作人员	工具ID
1	2
2	3
3	2
1	2

工具表数据

名称	数量
螺丝刀	2
悍马	3
扳手17	1
管子扳手	2

表数据

名称名称名称	lastname
andrew	Greekman
gordon	gordon
摘要表	gordon
Wingman	工人

Answer 1

您需要一个左外连接。
左外连接是：“从G.Defaultifempty（）的Tuusednone中”
请参阅下面的代码

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; }
    }
}