查询。从对象计数>n的表中选择

Question

我有表"Cars“与字段ModelName，制造商，年份，价格和表" Maker ”的字段名。我需要Linq到Sql查询，在这里我可以选择模型计数> n的所有制作者。

我试了一下：

Data.ItemsSource = (from m in db.GetTable<Maker>()
                    where (from c in db.GetTable<Car>() 
                           where c.Maker == m.Name 
                           select c).Count() > 1
                    select m.Name);

但没有结果。我怎样才能实现这一点？

UPD。表车代码

CREATE TABLE [dbo].[Car] (
    [CarId]     INT           IDENTITY (1, 1) NOT NULL,
    [ModelName] NVARCHAR (50) NOT NULL,
    [Equipment] NVARCHAR (50) NULL,
    [Year]      INT           NULL,
    [Price]     INT           NULL,
    [Cathegory] NCHAR (20)    DEFAULT ('Sedan') NOT NULL,
    [Maker]     NCHAR (20)    DEFAULT ('Toyota') NOT NULL,
    PRIMARY KEY CLUSTERED ([CarId] ASC),
    CONSTRAINT [FK_Car_ToCatheg] FOREIGN KEY ([Cathegory]) REFERENCES [dbo].[Catheg] ([Name]) ON DELETE CASCADE,
    CONSTRAINT [FK_Car_ToMaker] FOREIGN KEY ([Maker]) REFERENCES [dbo].[Maker] ([Name]) ON DELETE CASCADE
);

级车

 [Table(Name = "Car")]
    public class Car
    {
        [Column(IsPrimaryKey = true, IsDbGenerated = true)]
        public int CarId { get; set; }
        [Column]
        public string ModelName { get; set; }
        [Column]
        public string Equipment { get; set; }
        [Column]
        public int Year { get; set; }
        [Column]
        public int Price { get; set; }
        [Column]
        public string Maker { get; set; }
        [Column]
        public string Cathegory { get; set; }

        public Car()
        { }
        public Car(string modelName, string equipment, int year, int price, string cath, string carmaker, int id = -1)
        {
            ModelName = modelName;
            Equipment = equipment;
            Year = year;
            Price = price;
            Maker = carmaker;
            Cathegory = cath;
            CarId = id;
        }

        public override string ToString()
        {
            return ModelName + " " + Equipment + " " + Year + " " + Maker + " " + Price + " " + Cathegory;

        }

表制作者代码

CREATE TABLE [dbo].[Maker] (
    [Name] NCHAR (20) NOT NULL,
    PRIMARY KEY CLUSTERED ([Name] ASC)
);

阶级制造者

[Table(Name = "Maker")]
public class Maker
{
    [Column(IsPrimaryKey = true)]
    public string Name { get; set; }

}

Answer 1

这应该给你的制造者的名字与各自的计数。

var Results = from c in db.GetTable<Car>() 
group c by c.Maker into cg
join m in db.GetTable<Maker>() on cg.FirstOrDefault().Maker equals m.Name
where cg.Count() > 1
select new 
{
    MakerName = m.Name,
    Count = cg.Count()
};

Answer 2

如果您实际上是指每个Cars的数量，那么只需在Car.Maker上分组，Maker表就不需要了：

Data.ItemsSource = from c in db.GetTable<Car>()
                   group c by c.Maker into cg
                   where cg.Count() > 1
                   select cg.Key;

如果模型实际上独立于Year和Price，那么只需计算ModelName：

var models = (from c in db.GetTable<Car>()
             select new { c.Maker, c.ModelName }).Distinct();

Data.ItemsSource = from c in models
                   group c by c.Maker into cg
                   where cg.Count() > 1
                   select cg.Key;