无需密钥即可将一对多关系映射到现有数据库视图 [英] Mapping one to many relationship to existing database views without keys

问题描述

我需要在两个类之间创建一对多关系。数据库不包含主键或外键。因此，我决定创建2个视图而不是3个表，并且视图也没有主键/外键（不允许更改数据库架构）

I need to create one to many relationship between two classes.Database do not contains primary or foreign keys. So i decided to create 2 views instead of 3 tables, and views also has no primary/foreign key (Changing the database schema is not allowed)

视图

[Table("Barcode")]
public partial class Barcode
{
    [Column("_Barcode")]
    public string _Barcode { get; set; }

    [Column("_SkuRef")]
    [MaxLength(16)]
    public byte[] _SkuRef { get; set; }

    [Key]
    [Column("Id")]
    public long Id { get; set; }

    public string _SkuId { get; set; }
}


[Table("SKU")]
public partial class SKU
{
    [Column("_Code", Order = 0)]
    [StringLength(11)]
    public string _Code { get; set; }

    [Column("_Description", Order = 1)]
    [StringLength(150)]
    public string _Description { get; set; }

    [Column("_ProductId", Order = 2)]
    [StringLength(50)]
    public string _ProductId { get; set; }

    [Column("_Ref", Order = 3)]
    [MaxLength(16)]
    public byte[] _Ref { get; set; }

    [Key]
    [Column(Order = 4)]
    [StringLength(36)]
    public string Id { get; set; }
}

通过这种方式，我尝试使用类似

In this way, i tryed to use something like

using (SkuContext db = new SkuContext())
        {

            var Skutable = db.SKUs;
            foreach(var s in Skutable)
            {

                Console.WriteLine(s.Id);
            }
        } 

并且运行良好。但是，当我尝试添加

And its working well. But when im trying to add

 public partial class Barcode
 {
  ....
    [ForeignKey("_SkuId")]
    [Column("_SkuId")]
    [StringLength(36)]
    public string _SkuId { get; set; }
 }
 public partial class SKU
 {
  ....
    public IEnumerable<Barcode> Barcodes;
 }

我正面临
属性'_SkuId'无法配置为导航属性错误。
我的问题是如何做正确的事？如何在像这样的两个类之间建立关系？

I'm facing "The property '_SkuId' cannot be configured as a navigation property" error. And my question is how to do the right thing? How to create relationship between two classes like these ?

推荐答案

由于表不包含外键，因此出现错误。外键属性将无济于事，因为您在数据库中没有这种关系。

You got error because your table does not contains foreign key. The foreign key attribute will not help cause you don't have such relationship in database.

如果使用的是诸如实体框架之类的ORM，解决方案将是编写一个存储过程并使EF与其一起工作。

If you are using ORM, such as Entity Framework, the solution will be to write a stored procedure and to make EF to work with it.

另一种方法是使用 System.Data.SqlClient 中的类来实现自己的业务逻辑，具体阅读，编写等等。

Another way is to implement your own business logic using classes from System.Data.SqlClient by specific reading, writing, etc..

因此，我认为在您的简单情况下不需要进行此练习。但是，如果您只是以这种直截了当的方式进行操作，而又不更改表格，请准备好大量的文档。

So I consider that such an exercise does not needed in your simple situation. But if you are going just in this straight way, without changing tables, be ready for tons of documentation.

这篇关于无需密钥即可将一对多关系映射到现有数据库视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！