选择&在实体框架中更新多对多4 [英] Selecting & Updating Many-To-Many in Entity Framework 4
问题描述
我是相对较新的EF，并具有以下由资产和国家组成的实体模型。资产可以属于许多国家，因此与国家有多对多关系（在数据库中有一个连接表，两个字段都作为主键）。
我想要能够执行以下操作：
首先，当我检索资产（或资产）从模型我想要得到相关的国家。那么我可以将国家列表绑定到IEnumerable。以这种方式检索国家，为我提供了具有ToList（）扩展方法的国家对象的EntityCollection。所以不知道如果我正在这个正确的大道下去。这是我的GetAll方法：
public IEnumerable< Asset> GetAll（）
{
using（var context = CreateAssetContext（））
{
var assetEntities = context.Assets.Include（Countries）ToList（）;
return AssetMapper.FromEntityObjects（assetEntities）;
}
}
其次，我想要能够选择一个最近我想要更新给定资产的国家/地区列表。
非常感谢。
首先，当我检索资产）从模型我想要
得到它的相关国家。我会然后
喜欢将国家列表绑定到IEnumerable。
不知道我是否正确理解，但 EntityCollection< T> 实现 IEnumerable< T> ，所以你不必做任何特别的事情您可以在加载资产（包括国家/地区）后使用 Asset.Countries 。
其次我想要选择一个国家的列表，其中
AssetId ==一些值。
using（var context = CreateAssetContext（））
{
var countries = context.Countries
.Where（c => c.Assets.Any（a = > a.AssetId == givenAssetId））
.ToList（）;
}
或：
using（var context = CreateAssetContext（））
{
var countries = context.Assets
.Where（a => a.AssetId == givenAssetId）
.Select（a => a.Countries）
.SingleOrDefault（）;
}
第二个选项是OK（不确定它是否比SQL中的第一个更好观点），因为 AssetId 是主键，所以只能有一个资产。要查询其他条件 - 例如 Asset.Name ==XYZ - 您可以期待多个资产，我更喜欢第一个选项。对于第二个，您必须更换选择通过 SelectMany 和 SingleOrDefault by ToList 并使用 Distinct 过滤掉可能的重复国家/地区。 SQL可能会更复杂。
最后，我想要更新一个给定的
的资产列表。
这是比较棘手的，因为你需要处理的情况：1）国家已被添加到资产，2）国家有从资产中删除，3）国家已经与资产相关。
说你有国家ID的列表（ IEnumerable< int> countryIds ），并且您想将这些国家/地区与给定资产相关联：
using（var context = CreateAssetContext ）
{
var asset = context.Assets.Include（Countries）
.Where（a => a.AssetId == givenAssetId）
.SingleOrDefault（）;
if（asset！= null）
{
foreach（var country in asset.Countries.ToList（））
{
//检查是否现有国家是ID列表中的国家之一：
if（！countryIds.Contains（country.Id））
{
//与国家的关系已被删除
//从资产的国家/地区收集中移除
asset.Countries.Remove（country）;
}
}
foreach（countryID中的var id）
{
//检查id的国家/地区是否已经分配给资产：
if（！ asset.Countries.Any（c => c.CountryId == id））
{
//否：
//然后使用id创建stub对象并附加到上下文
var country = new Country {CountryId = id};
context.Countries.Attach（country）;
//然后添加到资产的国家集合
asset.Countries.Add（country）;
}
//是：不做任何
}
context.SaveChanges（）;
}
}
修改
对于数据库的第二次往返价格，您可以使用这个更简单的代码：
using（var context = CreateAssetContext（））
{
var asset = context.Assets.Include（Countries）
.Where（a => a.AssetId = = givenAssetId）
.SingleOrDefault（）;
if（asset！= null）
{
// second DB roundtrip
var countries = context.Countries
.Where（c => countryIds.Contains（c.CountryId））
.ToList（）;
asset.Countries = countries;
context.SaveChanges（）;
}
}
EF的变更检测应该识别已添加哪些国家/从资产的国家/地区列表中删除。我不是100％确定，如果后一个代码可以正常工作。
I am relatively new to the EF and have the following entity model above which consists of an Asset and a Country. An Asset can belong in many countries and thus has a many-to-many relationship with country (has a join table in the database with two fields both as primary keys).
I want to be able to do the following:
Firstly when I retrieve an asset (or assets) from the model I want to get the respective countries that its associated with. I would then like to be able to bind the countries list to an IEnumerable. Retrieving the countries in this way provides me with an EntityCollection of country objects which has extension method for ToList(). Therefore not sure If I am going down the right avenue with this one. Here is my GetAll method:
public IEnumerable<Asset> GetAll()
{
using (var context = CreateAssetContext())
{
var assetEntities = context.Assets.Include("Countries").ToList();
return AssetMapper.FromEntityObjects(assetEntities);
}
}
Secondly I want to be able to select a list of countries where the AssetId == some value.
Finally I want to be able to update the list of countries for a given Asset.
Many thanks.
Firstly when I retrieve an asset (or assets) from the model I want to get the respective countries that its associated with. I would then like to be able to bind the countries list to an IEnumerable.
Not sure if I understand that correctly, but EntityCollection<T> implements IEnumerable<T>, so you don't have to do anything special, you just can use Asset.Countries after you have loaded the assets including the countries.
Secondly I want to be able to select a list of countries where the AssetId == some value.
using (var context = CreateAssetContext())
{
var countries = context.Countries
.Where(c => c.Assets.Any(a => a.AssetId == givenAssetId))
.ToList();
}
Or:
using (var context = CreateAssetContext())
{
var countries = context.Assets
.Where(a => a.AssetId == givenAssetId)
.Select(a => a.Countries)
.SingleOrDefault();
}
The second option is OK (not sure if it's better than the first from SQL viewpoint) because AssetId is the primary key, so there can be only one asset. For querying by other criteria - for example Asset.Name == "XYZ" - where you could expect more than one asset I would prefer the first option. For the second you had to replace Select by SelectMany and SingleOrDefault by ToList and use Distinct to filter out possible duplicated countries. The SQL would probably be more complex.
Finally I want to be able to update the list of countries for a given Asset.
This is more tricky because you need to deal with the cases: 1) Country has been added to asset, 2) Country has been deleted from asset, 3) Country already related to asset.
Say you have a list of country Ids ( IEnumerable<int> countryIds ) and you want to relate those countries to the given asset:
using (var context = CreateAssetContext())
{
var asset = context.Assets.Include("Countries")
.Where(a => a.AssetId == givenAssetId)
.SingleOrDefault();
if (asset != null)
{
foreach (var country in asset.Countries.ToList())
{
// Check if existing country is one of the countries in id list:
if (!countryIds.Contains(country.Id))
{
// Relationship to Country has been deleted
// Remove from asset's country collection
asset.Countries.Remove(country);
}
}
foreach (var id in countryIds)
{
// Check if country with id is already assigned to asset:
if (!asset.Countries.Any(c => c.CountryId == id))
{
// No:
// Then create "stub" object with id and attach to context
var country = new Country { CountryId = id };
context.Countries.Attach(country);
// Then add to the asset's country collection
asset.Countries.Add(country);
}
// Yes: Do nothing
}
context.SaveChanges();
}
}
Edit
For the price of a second roundtrip to the database you can probably use this simpler code:
using (var context = CreateAssetContext())
{
var asset = context.Assets.Include("Countries")
.Where(a => a.AssetId == givenAssetId)
.SingleOrDefault();
if (asset != null)
{
// second DB roundtrip
var countries = context.Countries
.Where(c => countryIds.Contains(c.CountryId))
.ToList();
asset.Countries = countries;
context.SaveChanges();
}
}
EF's change detection should recognize which countries have been added or deleted from the asset's country list. I am not 100% sure though if the latter code will work correctly.
这篇关于选择&在实体框架中更新多对多4的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
