选择&更新实体框架 4 中的多对多

发布于 2024-12-09 21:59:32 字数 714 浏览 0 评论 0原文

在此处输入图像描述

我对 EF 比较陌生,上面有以下实体模型,其中包含资产和国家/地区。资产可以属于多个国家/地区,因此与国家/地区具有多对多关系(数据库中有一个连接表,其中两个字段均作为主键)。

我希望能够执行以下操作:

首先,当我从模型中检索资产(或多个资产)时,我想要获取与其关联的各个国家/地区。然后我希望能够将国家/地区列表绑定到 IEnumerable。以这种方式检索国家/地区为我提供了国家/地区对象的 EntityCollection,该对象具有 ToList() 的扩展方法。因此不确定我是否走在正确的道路上。这是我的 GetAll 方法:

public IEnumerable<Asset> GetAll()
{
    using (var context = CreateAssetContext())
    {
        var assetEntities = context.Assets.Include("Countries").ToList();
        return AssetMapper.FromEntityObjects(assetEntities);
    }
}

其次,我希望能够选择 AssetId == 某个值的国家/地区列表。

最后,我希望能够更新给定资产的国家/地区列表。

非常感谢。

enter image description here

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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

长途伴 2024-12-16 21:59:32

首先,当我从模型中检索一个或多个资产时,我想要
获取与其关联的各个国家。那时我会
希望能够将国家/地区列表绑定到 IEnumerable。

不确定我是否理解正确,但是 EntityCollection 实现了 IEnumerable,因此您不必执行任何特殊操作,只需使用 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();
}

第二个选项可以(从 SQL 角度不确定它是否比第一个更好),因为 AssetId 是主键,因此只能有一个资产。对于按其他条件查询 - 例如 Asset.Name == "XYZ" - 如果您可能期望多个资产,我更喜欢第一个选项。对于第二个,您必须将 Select 替换为 SelectMany 并将 SingleOrDefault 替换为 ToList 并使用 Distinct 过滤掉可能重复的国家/地区。 SQL 可能会更复杂。

最后,我希望能够更新给定的国家/地区列表
资产。

这比较棘手,因为您需要处理以下情况:1)国家/地区已添加到资产中,2)国家/地区已从资产中删除,3)国家/地区已与资产相关。

假设您有一个国家/地区 ID 列表 (IEnumerablecountryIds),并且希望将这些国家/地区与给定资产关联起来:

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();
    }
}

编辑

对于第二次往返的价格您可以使用以下更简单的代码来访问数据库:

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% 确定后面的代码是否能正常工作。

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.

两个我 2024-12-16 21:59:32

这里的具体问题是什么?难道你做不到吗?

您要选择资产中的国家还是拥有特定资产的国家?

要更新它很简单,只需更改内容,然后 context.SaveChanges() 将提交到数据库。

Whats the specific question here? Are you not being able to do that?

do you want to select the countries in an asset or the countries that have a certain asset?

to update its simple, just change stuff and then context.SaveChanges() will commit to the database.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文