none
ADO.NET Entity Framework 问题!这几种情况应该选择哪个? RRS feed

  • 常规讨论

  • Service 层的查询表示:

    第一行:var articles = db.Articles.Where(a => a.UserId == db.ArticleUsers.FirstOrDefault(u => u.UserName == username).UserId).OrderByDescending(a => a.CreateDate);

    第二行:var articles = (from a in db.Articles from u in db.ArticleUsers where u.UserName == username && a.UserId == u.UserId select a).OrderByDescending(a => a.CreateDate);

    第三行:var articles = db.Articles.Where(a => a.UserId == db.ArticleUsers.FirstOrDefault(u => u.UserName == username).UserId).OrderByDescending(a => a.CreateDate).Skip(5).Take(3);

    第四行:var articles = from a in db.Articles from u in db.ArticleUsers where u.UserName == username && a.UserId == u.UserId select new Article{ ArticleId = a.ArticleId, Body = a.Body, CreateDate = a.CreateDate};

    分页的泛型类:

     public class PaginatedList<T> : List<T>
        {
            public int PageIndex { get; private set; }
            public int PageSize { get; private set; }
            public int TotalCount { get; private set; }
            public int TotalPages { get; private set; }
            public string PageDigit { get; private set; }

            public bool HasPreviousPage { get { return (PageIndex > 0); } }
            public bool HasNextPage { get { return (PageIndex + 1 < TotalPages); } }

            public PaginatedList(IEnumerable<T> source, int pageIndex, int pageSize)
            {
                if (pageIndex < 0)
                    pageIndex = 0;
                PageIndex = pageIndex;
                PageSize = pageSize;
                if (source != null)
                {
                    TotalCount = source.Count();
                    TotalPages = (int)Math.Ceiling(TotalCount / (double)PageSize);

                    this.AddRange(source.Skip(PageIndex * PageSize).Take(PageSize));
                }
            }
        }

     

    Controller:

    var all = ArticleService.FindUserArticles(username);
    var model = new PaginatedList<Article>(all, id ?? 0, PageSize);

    return View(model);

     

    问题1:Service 里 articles 的2种写法,既 from a in db.XXXX select a 与 db.XXX.Where() 有什么区别?行1与行2生成的 sql 是否相同?性能哪个好?这两种写法一个是LINQ一个是导航属性吗?

    问题2:在 Service 里不写分页代码(skip()take()),而在调用时使用哪个泛型类,跟直接在 Service 中分页生成的 sql 不同,语句较为复杂我看不懂,这2种方案性能是一样的吗? 从 linq 的延迟查询介绍我理解为行1、2与行3的性能是一样的,都是从数据库取出部分数据,请问,这个对吗?因为生成的 sql 不一样啊。

    问题3:如何用导航属性完成行4中的查询部分列功能。

     问题4:如何查看 linq 取出的数据?是从数据库一次性读出所有,在内存里分页。还是在数据库分页读取。有没有记录数据库所提供数据的记录?最好是在sql里看。

    本人对AEF不熟,请高人指点。

    2010年8月15日 8:44

全部回复

  • 这个可以把查询转化成ObjectQuery用ToTraceString看

    排序、Skip和Take建议在L2E层做,这样可以利用数据库里面现有的索引,像第三行那样用Linq2Object在内存里面做性能太差。

     



    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP
    2010年8月15日 18:03
    版主
  • 这个可以把查询转化成ObjectQuery用ToTraceString看

    排序、Skip和Take建议在L2E层做,这样可以利用数据库里面现有的索引,像第三行那样用Linq2Object在内存里面做性能太差。

     



    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP


    第三行性能差?  晕了,出乎我的意料。

    var articles = db.Articles.Where(a => a.UserId == db.ArticleUsers.FirstOrDefault(u => u.UserName == username).UserId).OrderByDescending(a => a.CreateDate);

    var takes = articles.Skip(5).Take(7);

    你的意思是这样分开?而不是一句代码写完?

    2010年8月17日 3:40
  • 这个可以把查询转化成ObjectQuery用ToTraceString看

    排序、Skip和Take建议在L2E层做,这样可以利用数据库里面现有的索引,像第三行那样用Linq2Object在内存里面做性能太差。

     



    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP


    你的意思是说 db.Articles.Where(a => a.UserId == db.ArticleUsers.FirstOrDefault(u => u.UserName == username).UserId).OrderByDescending(a => a.CreateDate).Skip(5).Take(3);

    这样的表达式叫 L2O?

    db.Articles.Where(a => a.UserId == db.ArticleUsers.FirstOrDefault(u => u.UserName == username).UserId) 这部分就是 L2E?

     

    2010年8月17日 4:39
  • FirstOrDefault的返回值应该就是Object。之后的Lamda表达式应该是L2O

    除非数据量很小,否则在数据库里面分页、排序总是比较快的。



    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP
    2010年8月17日 4:48
    版主
  • FirstOrDefault的返回值应该就是Object。之后的Lamda表达式应该是L2O

    除非数据量很小,否则在数据库里面分页、排序总是比较快的。



    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP


    那实现这个需求要怎么写?

    以第三行为例。

    2010年8月17日 6:49
  • FirstOrDefault的返回值应该就是Object。之后的Lamda表达式应该是L2O

    除非数据量很小,否则在数据库里面分页、排序总是比较快的。



    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP


    或者说 L2O 跟 L2E 有什么关系,在一句表达式中怎么区分呢?

     

    2010年8月17日 6:50
  • 得看你的ArticleUser和Article有没有外键

    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP
    2010年8月17日 13:39
    版主
  • 有外键.

    var articles = db.ArticleUsers.FirstOrDefault(u=>u.UserName==username).Articles.OrderBy(a=>a.CreateDate);

    这种情况下如何区分 L2O   L2E?

    2010年8月20日 7:02
  • L2E的IQueryable可以转化成ObjectQuery<T>然后用ToTraceString输出SQL。

    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP
    2010年8月20日 15:52
    版主