c# - Entity Framework 避免延迟加载/已打开 DataReader 问题

Question

通过连续多次运行的联合查询，如果 Users 以起始字母存在，我会得到每个字母的 UserlistSection 对象。所以我得到这样的东西:

• 用户列表“A”部分
  • 用户
  • 用户
• 用户列表“H”部分
  • 用户
• 用户列表部分“Y”
  • 用户
  • 用户
  • 用户

但是查询抛出以下异常

MySql.Data.MySqlClient.MySqlException: There is already an open DataReader associated with this Connection which must be closed first.

查询任务

public UserRepository(myDatabase database)
    {
        _database = database;
    }

public Task<ObservableCollection<User>> GetUserFriendsAsync(int id, string letter)
    {
        return Task.Factory.StartNew(() =>
        {
            try
            {
                var query1 = (from urs in _database.user_relationships
                              join u in _database.users on urs.relationship_user_from equals u.user_id
                              where urs.relationship_user_to == id
                                    && u.user_name.StartsWith(letter)
                                    && urs.relationship_status == 1
                              select new User
                              {
                                  Id = u.user_id,
                                  Username = u.user_name,
                                  AvatarByte = u.user_avatar,
                                  Gender = u.user_gender,
                                  GroupId = u.user_usergroup,
                                  LoginStatusId = u.user_loginstatus
                              });

                var query2 = (from urs in _database.user_relationships
                              join u in _database.users on urs.relationship_user_to equals u.user_id
                              where urs.relationship_user_from == id
                                    && u.user_name.StartsWith(letter)
                                    && urs.relationship_status == 1
                              select new User
                              {
                                  Id = u.user_id,
                                  Username = u.user_name,
                                  AvatarByte = u.user_avatar,
                                  Gender = u.user_gender,
                                  GroupId = u.user_usergroup,
                                  LoginStatusId = u.user_loginstatus
                              });

                // Union to get users via relationships (from - to and to - from)
                var queryUnion = query1.Union(query2).OrderBy(u => u.Username);

                return new ObservableCollection<User>(queryUnion);
            }
            catch (Exception ex)
            {
                Trace.WriteLine("UserRepository - GetUserFriendsAsync" + ex);
                return null;
            }
        });
    }

和我创建集合的对象/方法

public class UserlistSection : ObservableObject
{
    #region Attributes

    private string _letter;
    private ObservableCollection<User> _users;

    #endregion Attributes

    public string Letter
    {
        get { return _letter; }
        set { Set(ref _letter, value); }
    }

    public ObservableCollection<User> Users
    {
        get { return _users; }
        set { Set(ref _users, value); }
    }
}

// -------------------------------------------------------------------------

 public async void UpdateFriendlistCollection()
    {
        IsLoading = true;
        Friendlist = new ObservableCollection<UserlistSection>();

        for (var c = 'A'; c <= 'Z'; c++)
        {
            try
            {
                var letter = c.ToString();
                var userCollection = await _userRepository.GetUserFriendsAsync(_userObj.Id, letter);

                if (userCollection.Count < 1) return;

                foreach (var friend in userCollection)
                {
                    friend.AvatarImage = friend.GetAvatarImgByPixel(200);
                }

                var section = new UserlistSection
                {
                    Letter = letter,
                    Users = userCollection
                };

                Friendlist.Add(section);
            }
            catch (Exception ex)
            {
                Trace.WriteLine("FriendlistViewModel - UpdateFriendlistCollection: " + ex.Message);
            }
        }

        IsLoading = false;
    }

可能有更好的方法来构建查询，但我还不知道怎么做。如果有人能看到我的错误以避免延迟加载/解决异常，那就太好了。

如果我遗漏了一些解释，请告诉我。

编辑

当我像那样添加 MultipleActiveResultSets=True 时，它告诉我不支持该关键字？

<connectionStrings>
<add name="someDatabase"   connectionString="metadata=res://*/Repository.Database.SomeDatabase.csdl|res://*/Repository.Database.SomeDatabase.ssdl|res://*/Repository.Database.SomeDatabase.msl;provider=MySql.Data.MySqlClient;provider connection string=&quot;server=127.0.0.1;user id=someUser;persistsecurityinfo=True;password=somePassword;multipleactiveresultsets=True;database=someDatabase;&quot;" providerName="System.Data.EntityClient" />

System.ArgumentException: Keyword not supported.
Parametername: multipleactiveresultsets
bei MySql.Data.MySqlClient.MySqlConnectionStringBuilder.GetOption(String key)
bei MySql.Data.MySqlClient.MySqlConnectionStringBuilder.set_Item(String  keyword, Object value)

Answer 1

您应该在配置的连接字符串中启用 MARS。

将 "MultipleActiveResultSets=True" 添加到连接字符串。

更多info .

编辑

来自 MSDN :

When you call the Load method during a foreach (C#) or For Each (Visual Basic) enumeration, the Entity Framework tries to open a new data reader. This operation will fail unless you have enabled multiple active results sets by specifying multipleactiveresultsets=true in the connection string. For more information, see Using Multiple Active Result Sets (MARS) on MSDN. You can also load the result of the query into a List collection, which closes the data reader and enables you to enumerate over the collection to load referenced entities

编辑

对于 MySQL，我找到了 this .也许它能帮到你。

MySQL好像不支持MARS。如果您可以为 MS SQL Server Express 更改数据库。或者更改 ORM，例如 NHibernate。