高级投影查询
本文关键字:查询 投影 高级 | 更新日期: 2023-09-27 18:35:09
有什么更好的方法可以做到这一点吗
using (var db = new SmartContext())
{
var MyQuery = from idr in db.ID_SB
join tk in db.Track_SB on idr.MB_Track_ID equals tk.MB_TrackID
join talr in db.Track_Album_Reln on tk.MB_TrackID equals talr.MB_Track_ID
join tal in db.Album_SB on talr.MB_AlbumID equals tal.MB_Release_ID
orderby idr.Last_played descending
select new
{
mb = tk.MB_TrackID,
Hash = idr.Hash,
Title = tk.Title,
Album = tal.Album_Name,
Times_Played = idr.Times_played,
Last_Played = idr.Last_played
};
string artist = "";
var list = new[] { new { Hash = "", Title = "", Album = "", Artist = "", Times_Played = "", Last_Played = ""}}.ToList();
list.Clear();
foreach (var q in MyQuery)
{
int i = 0;
var art = db.Track_Artist_Reln.Where(a => a.MB_Track_ID == q.mb);
foreach (var a in art)
{
var tart = db.Artist_SB.Where(ar => ar.MB_Artist_ID == a.MB_ArtistID).Select(ar => ar.Artist_Name);
foreach (var tar in tart)
{ if (i != 0) { artist = artist + ", " + tar; } else { artist = tar; i++; } }
}
list.Add(new
{
Hash = q.Hash.ToString(),
Title = q.Title.ToString(),
Album = q.Album.ToString(),
Artist = artist.ToString(),
Times_Played = q.Times_Played.ToString(),
Last_Played = q.Last_Played.ToString(),
});
}
ListView1.ItemsSource = list;
}
有没有更好的方法,因为它会消耗大量时间
此代码用于目的
在此我想检索
- 散 列
- 标题
- 专辑名称 艺术家
- (在单行字段艺术家中包含该歌曲的所有艺术家)
- 播放次数
- 上次播放时间
除了艺术家名字之外的所有内容,我只能通过加入所有内容来获得一切
有很多五月关系主表ID_SB、Track_SB、Album_SB、Artist_SB其他是存储两个表的主键的关系
内部循环可以替换为使用 string.Join(…)
和 .SelectMany()
将嵌套列表(实际上IEnumerable
)展平为一个:
var artist = string.Join(", ",
db.Track_Artist_Reln
.Where(a => a.MB_Track_ID == q.mb)
.SelectMany(a => db.Artist_SB
.Where(ar => ar.MB_Artist_ID == a.MB_ArtistID)
.Select(ar => ar.Artist_Name)));
您可以使用.ToList()
,而不是手动将枚举对象转换为列表:
var list = MyQuery.Select(q => new{
Hash = q.Hash.ToString(),
Title = q.Title.ToString(),
Album = q.Album.ToString(),
Artist = string.Join(", ",
db.Track_Artist_Reln
.Where(a => a.MB_Track_ID == q.mb)
.SelectMany(a => db.Artist_SB
.Where(ar => ar.MB_Artist_ID == a.MB_ArtistID)
.Select(ar => ar.Artist_Name))),
Times_Played = q.Times_Played.ToString(),
Last_Played = q.Last_Played.ToString(),
}.ToList();