我有一个数据库,里面有三个表--组织,位置和OrganisationLocations。组织保存公司信息,位置保存办公室地址信息,OrganisationLocations保存有关公司及其地址的多对多关系信息。例如,公司A可能是位置X和Y,公司B可能是位置Y和Z。
这些是生成的类
public partial class Locations
{
public Locations()
{
OrganisationLocations = new HashSet<OrganisationLocations>();
}
public long Id { get; set; }
public string Description { get; set; }
public string City { get; set; }
public string Street { get; set; }
public string PostCode { get; set; }
public string Name { get; set; }
public virtual ICollection<OrganisationLocations> OrganisationLocations { get; set; }
}public partial class Organisations
{
public Organisations()
{
OrganisationLocations = new HashSet<OrganisationLocations>();
}
public long Id { get; set; }
public string Name { get; set; }
public string Owner { get; set; }
public string ContactName { get; set; }
public string Email { get; set; }
public string Telephone { get; set; }
public virtual ICollection<OrganisationLocations> OrganisationLocations { get; set; }
}public partial class OrganisationLocations
{
public long Id { get; set; }
public long OrganisationId { get; set; }
public long LocationId { get; set; }
public virtual Locations Location { get; set; }
public virtual Organisations Organisation { get; set; }
}DB上下文如下所示:
public class PfApiContext : DbContext
{
public PfApiContext(DbContextOptions<PaymentAPIContext> options)
: base(options)
{
}
public DbSet<Locations> Locations { get; set; }
public DbSet<Organisations> Organisations { get; set; }
public virtual DbSet<OrganisationLocations> OrganisationLocations { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<OrganisationLocations>(entity =>
{
entity.HasOne(d => d.Location)
.WithMany(p => p.OrganisationLocations)
.HasForeignKey(d => d.LocationId)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_84m7dbl1gv1p6tg1wquwm8j5u");
entity.HasOne(d => d.Organisation)
.WithMany(p => p.OrganisationLocations)
.HasForeignKey(d => d.OrganisationId)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_r0mkkndb6c2tr9nl0rgjm068t");
});
}
}例如,我如何获取位置X中所有公司的所有公司数据?在查询这一点时,我实际上知道LocationID,所以SQL应该是
SELECT * FROM [dbo].[Organisations] WHERE Id IN (SELECT [OrganisationId] FROM [dbo].[OrganisationLocations] WHERE [LocationId] = 1)这可能真的很简单,我只是在做傻事,但我是EFCore和LINQ的新手,它的语法让我摸不着头脑。根据我能理解的位置名称选择位置
var locationUsers = await _pfApiContext.UserLocations.Where
(o => o.LocationName == locationName).ToListAsync();但这把我搞糊涂了。
谢谢你的帮助
发布于 2019-01-19 01:45:32
我将从所有三个类中删除虚拟集合和对象,然后删除OrganisationLocations表中的Id字段(假设它是主键)。OrganisationLocations中的OrganisationId和LocationId字段应该既是主键又是外键,以便在OrganisationLocations、Location和Organisation之间建立多对多关系。然后,您可以使用Linq执行一个简单的连接查询来获取您正在寻找的信息。例如:
var orgs = (from ol in PfApiContext.OrganisationLocations
inner join o in PfApiContext.Organisation on o.Id equals ol.OrganisationId
inner join l in PfApiContext.Locations on ol.LocationId equals l.Id
where ol.LocationId = 1
select new Organisation()
{
Id = o.Id,
Name = o.Name,
Owner = o.Ower,
etc...
}https://stackoverflow.com/questions/54255543
复制相似问题