asp.net-mvc – 实体框架包含OrderBy随机生成重复数据

当我从包含一些孩子(通过.IncludE)的数据库中检索到一个项目列表，并且随机订购时，EF给我一个意想不到的结果..我创建/克隆添加项目..

为了更好地解释自己，我创建了一个简单的EF CodeFirst项目来重现问题。
首先我会给你这个项目的代码。

该项目

创建一个基本的MVC3项目，并通过Nuget添加EntityFramework.SqlServerCompact包。
这增加了以下软件包的最新版本：

> EntityFramework v4.3.0
> SqlServerCompact v4.0.8482.1
> EntityFramework.SqlServerCompact v4.1.8482.2
> WeBACtivator v1.5

模型和DbContext

using System.Collections.Generic;
using System.Data.Entity;

namespace RandomWithInclude.Models
{
    public class PeopleContext : DbContext
    {
        public DbSet<Person> Persons { get; set; }
        public DbSet<Address> Addresses { get; set; }
    }

    public class Person
    {
        public int id { get; set; }
        public String Name { get; set; }

        public virtual ICollection<Address> Addresses { get; set; }
    }

    public class Address
    {
        public int id { get; set; }
        public String adressLine { get; set; }

        public virtual Person Person { get; set; }
    }
}

数据库设置和种子数据：EF.SqlServerCompact.cs

using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using RandomWithInclude.Models;

[assembly: WeBACtivator.PreApplicationStartMethod(typeof(RandomWithInclude.App_Start.EF),"Start")]

namespace RandomWithInclude.App_Start
{
    public static class EF
    {
        public static void Start()
        {
            Database.DefaultConnectionFactory = new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0");
            Database.SeTinitializer(new DbInitializer());
        }
    }
    public class DbInitializer : DropCreateDatabaseAlways<PeopleContext>
    {
        protected override void Seed(PeopleContext context)
        {
            var address1 = new Address {adressLine = "Street 1,City 1"};
            var address2 = new Address {adressLine = "Street 2,City 2"};
            var address3 = new Address {adressLine = "Street 3,City 3"};
            var address4 = new Address {adressLine = "Street 4,City 4"};
            var address5 = new Address {adressLine = "Street 5,City 5"};
            context.Addresses.Add(address1);
            context.Addresses.Add(address2);
            context.Addresses.Add(address3);
            context.Addresses.Add(address4);
            context.Addresses.Add(address5);
            var person1 = new Person {name = "Person 1",Addresses = new List<Address> {address1,address2}};
            var person2 = new Person {name = "Person 2",Addresses = new List<Address> {address3}};
            var person3 = new Person {name = "Person 3",Addresses = new List<Address> {address4,address5}};
            context.Persons.Add(person1);
            context.Persons.Add(person2);
            context.Persons.Add(person3);
        }
    }
}

控制器：HomeController.cs

using System;
using System.Data.Entity;
using System.Linq;
using System.Web.Mvc;
using RandomWithInclude.Models;

namespace RandomWithInclude.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            var db = new PeopleContext();
            var persons = db.Persons
                                .Include(p => p.Addresses)
                                .orderBy(p => Guid.NewGuid());

            return View(persons.ToList());
        }
    }
}

视图：Index.cshtml

@using RandomWithInclude.Models
@model IList<Person>

<ul>
    @foreach (var person in Model)
    {
        <li>
            @person.Name
        </li>
    }
</ul>

这应该是全部，你的应用程序应该编译:)

问题

你可以看到，我们有2个简单的模型(个人和地址)，而人可以有多个地址。
我们种下生成的数据库3人和5地址。
如果我们从数据库中获取所有的人员，包括地址和随机化的结果，并打印出这些人的名字，那就是错误的地方。

结果，我有时会得到4个人，有时5个，有时3个，我期望3.总是。
例如。：

>人1
>人3
>人1
>人3
人2

所以..它的复制/克隆数据！这不是很酷..
看来EF似乎没有跟踪哪个地址是哪个人的孩子。

生成的SQL查询是这样的：

SELECT 
    [Project1].[ID] AS [ID],[Project1].[Name] AS [Name],[Project1].[C2] AS [C1],[Project1].[ID1] AS [ID1],[Project1].[adressLine] AS [adressLine],[Project1].[Person_ID] AS [Person_ID]
FROM ( SELECT 
    NEWID() AS [C1],[Extent1].[ID] AS [ID],[Extent1].[Name] AS [Name],[Extent2].[ID] AS [ID1],[Extent2].[adressLine] AS [adressLine],[Extent2].[Person_ID] AS [Person_ID],CASE WHEN ([Extent2].[ID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
    FROM  [People] AS [Extent1]
    LEFT OUTER JOIN [Addresses] AS [Extent2] ON [Extent1].[ID] = [Extent2].[Person_ID]
)  AS [Project1]
ORDER BY [Project1].[C1] ASC,[Project1].[ID] ASC,[Project1].[C2] ASC

解决方法

>如果我从查询中删除.Include(p => p.Addresses)，一切顺利。但是当然地址没有加载，并且访问该集合将每次都对数据库进行新的调用。
>我可以先从数据库获取数据，然后通过在.orderBy之前添加一个.ToList()来随机化。如下所示：var persons = db.Persons.Include(p => p.Addresses).ToList( ).orderBy(p => Guid.NewGuid());

有没有人知道为什么会这样发生？
这可能是SQL生成中的错误吗？

var rnd = (new Random()).NextDouble();
var persons = db.Persons
    .Include(p => p.Addresses)
    .orderBy(p => SqlFunctions.checksum(p.Id * rnd))
    // Uniqueness of ordering ranking must be ensured.
    .ThenBy(p => p.Id);