实体框架优先代码:查询没有主键的视图

问题描述

我们的客户已授予对未定义主键的视图的访问权限.我知道实体框架需要一个主键供表识别.

Our customer has given the access to views in which there is no primary key is defined. I know Entity Framework needs a primary key for table to identify.

但是对于没有主键的视图，仍然可以查询.

But for views not having primary key is it still possible to query.

我尝试查找，但是实体框架总是给出错误提示:

I try to find but always Entity Framework gives error saying:

我知道键对于表很重要，但是对于仅读取视图来说，有任何hack或方法可以读取值而无需修改视图本身.

I understand key is important for tables, but for views just to read is there any hack or way to read the values without modifying the view itself.

推荐答案

在Entity Framework中不可能没有主键的实体.

It's not possible in Entity Framework to have Entities without primary key.

尝试从视图中获取可能的唯一键，合并各列，以创建唯一的主键.

Try to get a possible unique key from the views, combining columns, ... to create a unique primary key.

如果不可能的话，有一种解决方法，如果只是一个可查询的视图，则不需要对检索到的值(例如删除或更新)执行其他操作.修改视图以添加NEWID()，它将为每一行生成一个唯一的GUID ID，将此新列用作实体的主键.

If is not possible there is a workaround, if is only a queryable view, with out need to do other operations with retrieved values such delete or update. Modify the view to add NEWID() , it will generate a unique GUID ID for each row, use this new column as primary key for your entity.

CREATE VIEW FooView AS
    SELECT SELECT SYS_GUID() AS ID,
           COLUMN_A,
           COLUMN_B
     .....

问题是，如果您每次都重复同一查询，则同一行将获得不同的ID.

The problem is if you repeat the same query every time you will get different ID for the same row.

已更新

如果您无法修改视图，则可以将Entity与原始Sql一起使用，将原始sql创建为

If you can't not modify the view you can use Entity with a raw Sql, create the raw sql as

List<MyView> myViewItems = context.MyView.SqlQuery("SELECT SYS_GUID() AS ID, MyView.* FROM MyView").ToList();

在模型中添加

public Guid ID { get; set; }

并将新属性配置为主键.

And configure the new property as the primary key.

但是要小心，因为没有使用这种代码进行编译检查.

But be careful, because there is not compilation check with this kind of code.

这篇关于实体框架优先代码:查询没有主键的视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！