在EF Core中联接两个表，列名无效 [英] Joining two tables in EF Core, invalid column name
本文介绍了在EF Core中联接两个表，列名无效的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我试图通过linq语法通过以下方式在EntityFramework Core中联接两个表:
I am trying to join two tables in EntityFramework Core using linq syntax in the following way:
var results = dc.EntityA.Join(dc.EntityB, a => a.StringProperty, b => b.StringProperty, (a, b) => DoSomeStuff(a, b)).ToList();
StringProperty是数据库中的字符串列.我遇到了错误
Where StringProperty is a string column in the database. I am getting the error
{"Invalid column name 'EntityId'."}
我发现了类似的问题实体框架:无效的列名'OrganizationStructure_ID'建议在ModelCreation期间像这样设置外键
I found this similar question Entity Framework: Invalid column name 'OrganizationStructure_ID' where it is suggested to set the foreignkeys during ModelCreation like this
modelBuilder.Entity<EntityA>()
.HasOne(a => a.StringProperty)
.WithOne().HasForeignKey<EntityB>(b => b.StringProperty);
尝试此操作时出现错误:
When I try this I get the error:
Property or navigation with the same name already exists on entity type
任何建议如何解决此问题?
Any suggestions how to fix this?
推荐答案
从您提供的信息中很难说出实际问题所在.但是，这是一个非常简单的工作控制台项目，可以根据需要显式使用 Join():
Difficult to say from the information you gave, where the actual issue lies. But here is a very simple working console project, that explicitly uses Join() as you want:
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
namespace IssueConsoleTemplate
{
public class IceCream
{
public int IceCreamId { get; set; }
public string Name { get; set; }
public string BrandName { get; set; }
}
public class IceCreamBrand
{
public int IceCreamBrandId { get; set; }
public string Name { get; set; }
}
public class Context : DbContext
{
public DbSet<IceCream> IceCreams { get; set; }
public DbSet<IceCreamBrand> IceCreamBrands { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(@"Data Source=.\MSSQL14;Integrated Security=SSPI;Initial Catalog=So62853243")
.UseLoggerFactory(
LoggerFactory.Create(
b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<IceCreamBrand>()
.HasData(
new IceCreamBrand {IceCreamBrandId = 1, Name = "Cold as Ice"},
new IceCreamBrand {IceCreamBrandId = 2, Name = "Sweet as Sweets"});
modelBuilder.Entity<IceCream>()
.HasData(
new IceCream {IceCreamId = 1, Name = "Vanilla", BrandName = "Cold as Ice"},
new IceCream {IceCreamId = 2, Name = "Chocolate", BrandName = "Cold as Ice"},
new IceCream {IceCreamId = 3, Name = "Vanilla", BrandName = "Sweet as Sweets"});
}
}
internal static class Program
{
private static void Main()
{
using var context = new Context();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var iceCreamWithBrands = context.IceCreams.Join(
context.IceCreamBrands,
i => i.BrandName,
b => b.Name,
(i, b) => new {IceCream = i, Brand = b})
.Where(j => j.IceCream.BrandName == "Cold as Ice")
.OrderBy(j => j.IceCream.IceCreamId)
.ThenBy(j => j.Brand.IceCreamBrandId)
.ToList();
Debug.Assert(iceCreamWithBrands.Count == 2);
Debug.Assert(iceCreamWithBrands[0].IceCream.Name == "Vanilla");
Debug.Assert(iceCreamWithBrands[0].Brand.Name == "Cold as Ice");
}
}
}
它将生成以下SQL查询:
It generates the following SQL query:
SELECT [i].[IceCreamId], [i].[BrandName], [i].[Name], [i0].[IceCreamBrandId], [i0].[Name]
FROM [IceCreams] AS [i]
INNER JOIN [IceCreamBrands] AS [i0] ON [i].[BrandName] = [i0].[Name]
WHERE [i].[BrandName] = N'Cold as Ice'
ORDER BY [i].[IceCreamId], [i0].[IceCreamBrandId]
如果您的字符串属性是唯一的(但不是主键)，则还可以通过以下方式定义它们并使用 Include():
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
namespace IssueConsoleTemplate
{
public class IceCream
{
public int IceCreamId { get; set; }
public string Name { get; set; }
public string BrandName { get; set; }
public IceCreamBrand Brand { get; set; }
}
public class IceCreamBrand
{
public int IceCreamBrandId { get; set; }
public string Name { get; set; }
public ICollection<IceCream> IceCreams { get; set; } = new HashSet<IceCream>();
}
public class Context : DbContext
{
public DbSet<IceCream> IceCreams { get; set; }
public DbSet<IceCreamBrand> IceCreamBrands { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(@"Data Source=.\MSSQL14;Integrated Security=SSPI;Initial Catalog=So62853243")
.UseLoggerFactory(
LoggerFactory.Create(
b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<IceCream>(
entity =>
{
entity.Property(i => i.BrandName)
.IsRequired();
entity.HasOne(i => i.Brand)
.WithMany(b => b.IceCreams)
.HasForeignKey(i => i.BrandName)
.HasPrincipalKey(b => b.Name);
entity.HasData(
new IceCream {IceCreamId = 1, Name = "Vanilla", BrandName = "Cold as Ice"},
new IceCream {IceCreamId = 2, Name = "Chocolate", BrandName = "Cold as Ice"},
new IceCream {IceCreamId = 3, Name = "Vanilla", BrandName = "Sweet as Sweets"});
});
modelBuilder.Entity<IceCreamBrand>(
entity =>
{
entity.HasAlternateKey(b => b.Name);
entity.Property(e => e.Name)
.IsRequired();
entity.HasData(
new IceCreamBrand {IceCreamBrandId = 1, Name = "Cold as Ice"},
new IceCreamBrand {IceCreamBrandId = 2, Name = "Sweet as Sweets"});
});
}
}
internal static class Program
{
private static void Main()
{
using var context = new Context();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var iceCreamWithBrands = context.IceCreams
.Include(i => i.Brand)
.Where(i => i.Brand.Name == "Cold as Ice")
.OrderBy(i => i.IceCreamId)
.ThenBy(i => i.Brand.IceCreamBrandId)
.ToList();
Debug.Assert(iceCreamWithBrands.Count == 2);
Debug.Assert(iceCreamWithBrands[0].Name == "Vanilla");
Debug.Assert(iceCreamWithBrands[0].Brand.Name == "Cold as Ice");
}
}
}
它将生成以下SQL查询:
It generates the following SQL query:
SELECT [i].[IceCreamId], [i].[BrandName], [i].[Name], [i0].[IceCreamBrandId], [i0].[Name]
FROM [IceCreams] AS [i]
INNER JOIN [IceCreamBrands] AS [i0] ON [i].[BrandName] = [i0].[Name]
WHERE [i0].[Name] = N'Cold as Ice'
ORDER BY [i].[IceCreamId], [i0].[IceCreamBrandId]
最后，如果string属性是主键，则它变得更加简单:
Finally, in case the string property is the primary key, then it gets even simpler:
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
namespace IssueConsoleTemplate
{
public class IceCream
{
public string Name { get; set; }
public string BrandName { get; set; }
public IceCreamBrand Brand { get; set; }
}
public class IceCreamBrand
{
public string Name { get; set; }
public ICollection<IceCream> IceCreams { get; set; } = new HashSet<IceCream>();
}
public class Context : DbContext
{
public DbSet<IceCream> IceCreams { get; set; }
public DbSet<IceCreamBrand> IceCreamBrands { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(@"Data Source=.\MSSQL14;Integrated Security=SSPI;Initial Catalog=So62853243")
.UseLoggerFactory(
LoggerFactory.Create(
b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<IceCream>(
entity =>
{
entity.HasKey(i => new {i.Name, i.BrandName});
entity.HasOne(i => i.Brand)
.WithMany(b => b.IceCreams)
.HasForeignKey(i => i.BrandName);
entity.HasData(
new IceCream {Name = "Vanilla", BrandName = "Cold as Ice"},
new IceCream {Name = "Chocolate", BrandName = "Cold as Ice"},
new IceCream {Name = "Vanilla", BrandName = "Sweet as Sweets"});
});
modelBuilder.Entity<IceCreamBrand>(
entity =>
{
entity.HasKey(b => b.Name);
entity.HasData(
new IceCreamBrand {Name = "Cold as Ice"},
new IceCreamBrand {Name = "Sweet as Sweets"});
});
}
}
internal static class Program
{
private static void Main()
{
using var context = new Context();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var iceCreamWithBrands = context.IceCreams
.Include(i => i.Brand)
.Where(i => i.Brand.Name == "Cold as Ice")
.OrderBy(i => i.Name)
.ThenBy(i => i.Brand.Name)
.ToList();
Debug.Assert(iceCreamWithBrands.Count == 2);
Debug.Assert(iceCreamWithBrands[0].Name == "Chocolate");
Debug.Assert(iceCreamWithBrands[0].Brand.Name == "Cold as Ice");
}
}
}
它将生成以下SQL查询:
It generates the following SQL query:
SELECT [i].[Name], [i].[BrandName], [i0].[Name]
FROM [IceCreams] AS [i]
INNER JOIN [IceCreamBrands] AS [i0] ON [i].[BrandName] = [i0].[Name]
WHERE [i0].[Name] = N'Cold as Ice'
ORDER BY [i].[Name], [i0].[Name]
这篇关于在EF Core中联接两个表，列名无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
