使用LINQ到SQL的子查询

本文关键字：查询 SQL LINQ 使用 | 更新日期: 2023-09-27 18:05:20

我在c#教程项目中使用LINQ to SQL，但我对它有基本的了解。

我做了这样一个SQL查询:

SELECT ID,HeroName,HeroRarity,Initiative,Attack,Attack1 
FROM   CharactersName
WHERE  ID IN(
             SELECT HeroID 
             FROM   Hero_Group 
             WHERE GroupID=1
            )

(Hero_Group)是一个处理(CharactersName)表和另一个名为(Groups)的表之间的多对多关系的表，其中一个字符可以在多个组中。

我试着在LINQ中这样写:

void FilterGroup()
    {
        HDAEntities db = new HDAEntities();
        var query = from obj in db.CharactersNames
                    where obj.ID == from obj2 in db.Hero_Group
                                    where obj2.GroupID == comboBox1.SelectedIndex
                                    select new
                                    {
                                        obj2.GroupID
                                    }
                    select new
                    {
                        obj.ID,
                        obj.HeroName,
                        obj.HeroRarity,
                        obj.Initiative,
                        obj.Attack,
                        obj.Attack1
                    };
    }

当然这是胡言乱语。

有人能帮我一下吗?(被告知我对LINQ to SQL知之甚少)

~ TIA ~

使用LINQ到SQL的子查询

你可以这样做:

void FilterGroup()
{
    HDAEntities db = new HDAEntities();
    var subQuery = db.Hero_Group.Where(h => h.GroupID == comboBox1.selectedIndex)
                                .Select(h => h.GroupID);
    var query = from obj in db.CharactersNames
                where subQuery.Contains(obj.ID)
                select new
                {
                    obj.ID,
                    obj.HeroName,
                    obj.HeroRarity,
                    obj.Initiative,
                    obj.Attack,
                    obj.Attack1
                };
    var result = query.ToList(); // this is where your query and subquery are evaluated and sent to the database
    db.Dispose();
    db = null;
}

注意，在调用ToList()之前，子查询不会求值。您还需要处理对象(或者尝试使用using语句来创建HDAEntities对象)。此外，请确保在计算查询之前不要dispose db(在dispose之后调用ToList将抛出异常)。

    var query = from obj in db.CharactersNames
                where (from obj2 in db.Hero_Group
                       where obj2.GroupID == comboBox1.SelectedIndex
                       select new {obj2.GroupID}).Contains(obj.ID)
                select new
                {
                    obj.ID,
                    obj.HeroName,
                    obj.HeroRarity,
                    obj.Initiative,
                    obj.Attack,
                    obj.Attack1
                };

var query = from obj in db.CharactersNames
                where (from obj2 in db.Hero_Group 
                       where obj2.GroupID == comboBox1.SelectedIndex
                       select new {obj2.GroupID}).Contains(obj.ID)
                select new
                {
                    obj.ID,
                    obj.HeroName,
                    obj.HeroRarity,
                    obj.Initiative,
                    obj.Attack,
                    obj.Attack1
                };
相关文章：