首页 新闻 搜索 专区 学院

关于LINQ TO SQL查询问题,高手进来帮忙解决,谢谢

0
悬赏园豆:5 [已解决问题] 解决于 2012-09-14 14:20

实体类,EFCodeFirst

板块类
 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using System.ComponentModel.DataAnnotations;
 6 using System.Data.Entity;
 7 
 8 namespace Mydream.Entity
 9 {
10     [Table("tb_board")]
11     public class BoardEntity
12     {
13         [Key]
14         public int BoardID { set; get; }
15 
16         [Required,MaxLength(255)]
17         public string Title { set; get; }
18 
19         [Required, MaxLength(500)]
20         public string Keywords { set; get; }
21 
22         [Required, MaxLength(500)]
23         public string Desc { set; get; }
24 
25         public int Sort { set; get; }
26 
27         public bool IsDisplay { set; get; }
28 
29         public bool IsDelete { set; get; }
30 
31         [Required, Column(TypeName = "smalldatetime")]
32         public DateTime AddDate { set; get; }
33 
34         public virtual ICollection<BoardItemEntity> ItemBoardEntitys { set; get; }
35     }
36 }
子板块类
 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using System.ComponentModel.DataAnnotations;
 6 using System.Data.Entity;
 7 
 8 namespace Mydream.Entity
 9 {
10     [Table("tb_board_item")]
11     public class BoardItemEntity
12     {
13         [Key]
14         public int BoardItemID { set; get; }
15 
16         public int BoardID { set; get; }
17 
18         [MaxLength(255)]
19         public string Title { set; get; }
20 
21         [MaxLength(500),Column(TypeName="varchar")]
22         public string Photo { set; get; }
23 
24         [Required,MaxLength(500)]
25         public string Keywords { set; get; }
26 
27         [Required, MaxLength(500)]
28         public string Desc { set; get; }
29 
30         public int Sort { set; get; }
31 
32         public bool IsDisplay { set; get; }
33 
34         public bool IsDelete { set; get; }
35 
36         [Required, Column(TypeName = "smalldatetime")]
37         public DateTime AddDate { set; get; }
38     }
39 }

我在查询出板块类列表的时候,希望求出子版块类的列表,因为是一对多的关系,我板块类有排序字段,子板块类也有排序字段,并且都只查询部分字段,一下方法如何改进,谢谢!

 1 using System.Text;
 2 
 3 using Mydream.Entity;
 4 using Mydream.Entity.Context;
 5 using Mydream.Common;
 6 using Mydream.CacheDependencyFactory;
 7 
 8 namespace Mydream.WebLogic
 9 {
10     public class BoardBLL
11     {
12         MydreamContext db = new MydreamContext();
13 
14         public IEnumerable<BoardEntity> GetBoardList()
15         {
16             IEnumerable<BoardEntity> list = null;
17             var query = from b in db.BoardEntitys
18                         where b.IsDelete == false && b.IsDisplay == true
19                         orderby b.Sort, b.AddDate ascending
20                         select new
21                         {
22                             b.BoardID,
23                             b.Title,
24                             b.ItemBoardEntitys
25                         };
26             list = query.ToList().ConvertAll<BoardEntity>(item => new BoardEntity { BoardID = item.BoardID, Title = item.Title, ItemBoardEntitys = item.ItemBoardEntitys });
27             return list;
28         }
29     }
30 }

望高手解决,谢谢!

xu_happy_you的主页 xu_happy_you | 菜鸟二级 | 园豆:222
提问于:2012-09-07 16:58
< >
分享
最佳答案
0

var query = from b in db.BoardEntitys
18                         where b.IsDelete == false && b.IsDisplay == true
19                         orderby b.Sort, b.AddDate ascending
20                         select new BoardEntity
21                         {
22                             BoardID = b.BoardID,
23                             Title = b.Title,
24                             ItemBoardEntitys = b.ItemBoardEntitys
25                         };

这样行吗?

收获园豆:4
Launcher | 高人七级 |园豆:45045 | 2012-09-07 17:03

The entity or complex type 'Mydream.Entity.Context.BoardEntity' cannot be constructed in a LINQ to Entities query.

xu_happy_you | 园豆:222 (菜鸟二级) | 2012-09-07 17:13

@xu_happy_you: 你看看这个:http://msdn.microsoft.com/zh-cn/library/bb361109

就是不知道导航属性是否也能自动转换,你可以试下。

再不行的话,你就用AutoMapper吧,把public IEnumerable<BoardEntity> GetBoardList

改为 public IEnumerable<BoardEntityDataObject> GetBoardList

BoardEntityDataObject 只包含 BoardEntity的部分属性。

Launcher | 园豆:45045 (高人七级) | 2012-09-07 17:21

@Launcher: 我上面的例子,子板块列表其实求出来了,只是里面我不想查询子版块所有的列,还有就是子版块如何排序问题,这个问题真不知道如何解决,原来一直写SQL语句,现在用LINQ 还下不了手了,只会操作单表,

xu_happy_you | 园豆:222 (菜鸟二级) | 2012-09-07 17:23

@xu_happy_you: 你看这样行不:

17             var query = from b in db.BoardEntitys
18                         where b.IsDelete == false && b.IsDisplay == true
19                         orderby b.Sort, b.AddDate ascending
20                         select new
21                         {
22                             b.BoardID,
23                             b.Title,
24                             b.ItemBoardEntitys.OrderBy(o=>o.Sort)
25                         };

Launcher | 园豆:45045 (高人七级) | 2012-09-07 17:37

@Launcher: 

这样不行

xu_happy_you | 园豆:222 (菜鸟二级) | 2012-09-07 20:12

@xu_happy_you: 麻烦你下回说清楚为啥不行,我就是示例一下,你可以自己稍微改下嘛:

var query = from b in list
                        where b.IsDelete == false && b.IsDisplay == true
                        orderby b.Sort, b.AddDate ascending
                        select new
                        {
                           BoardID = b.BoardID,
                          Title=  b.Title,
                           ItemBoardEntitys = b.ItemBoardEntitys.OrderBy(o=>o.Sort)
                        };

Launcher | 园豆:45045 (高人七级) | 2012-09-10 09:27
其他回答(1)
0

1.不想查出所有子类,使用连接,左连接,select new{你要的字段}

2.排序 如果没有使用 top等进行过滤就不要在数据库排序了,在查询出来后使用时 进行显示排序,记得select 出排序字段。

收获园豆:1
Qlin | 园豆:2403 (老鸟四级) | 2012-09-07 18:01

能否帮我写下,谢谢!

支持(0) 反对(0) xu_happy_you | 园豆:222 (菜鸟二级) | 2012-09-08 09:19
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册