LINQ query in C#?

Question

I'm pretty new to C# and Linq programming world. I Want to do something similar to this, but SubType is a FK to table Type and I can't do what I've done in this example:

public static List<DropdownModel> GetSubTypes(List<string> ListTypes)
    {
        List<DropdownModel> SubTypes = new List<DropdownModel>();
        using (DocumentXtractorEntities DataBase = new DocumentXtractorEntities())
        {
            foreach (string TypeID in ListTypes)
            {
                int TypeIDINT = Int32.Parse(TypeID);
                SubTypes.AddRange((from C in DataBase.SubType.Where(s => s.Active && s.TypeID == TypeIDINT)
                            select new DropdownModel()
                            {
                                ID = C.SubTypeID,
                                Description = C.Name,
                                Selected = false
                            }).ToList());
            }
        }
        return SubTypes;
    }

So, the code above kinda filters the subtype text box when I chosen one or more Types. Now, I need to do the opposite, fill the Type List when subtypes are chosen.

I've tried something but I know that isn't possible the way I'm doing this. My code for now is this:

    public static List<DropdownModel> GetTypesBySubTypes(List<string> ListSubTypes)
    {
        List<DropdownModel> Types = new List<DropdownModel>();
        using (DocumentXtractorEntities DataBase = new DocumentXtractorEntities())
        {
            foreach (string SubTypeID in ListSubTypes)
            {
                int SubTypeIDINT = Int32.Parse(SubTypeID);
                Types.AddRange((from C in DataBase.Type.Where(s => s.Active && s.SubType.Contains(SubTypeIDINT))
                                select new DropdownModel()
                                {
                                    ID = C.TypeID,
                                    Description = C.Name,
                                }).ToList());
            }
        }
        return Types;
    }

[EDIT]

I've manage to do a sql query to do the job:

select T.TypeID from Type T join SubType ST on St.TypeID=T.TypeID
where ST.SubTypeID=3

But I don't know how to transform that to a linq query and do a Type.AddRange().

Can someone help me with that?

Answer 1

You can use the Intersect method to find the types that include any subtypes from the list of provided subtypes. This also eliminates the need to iterate using foreach and leaving that for Linq to handle.

List<int> subTypes = ListSubTypes.Select(s => int.Parse(s)).ToList();
DataBase.Type.Where(s => s.SubType.Select(st => st.SubTypesID).Intersect(subTypes).Any())

Here's an example based off of your code.

public static List<DropdownModel> GetTypesBySubTypes(List<string> ListSubTypes)
    {
        List<DropdownModel> Types = new List<DropdownModel>();
        List<int> subTypes = ListSubTypes.Select(s => int.Parse(s)).ToList();

        using (DocumentXtractorEntities DataBase = new DocumentXtractorEntities())
        {
                Types.AddRange((from C in DataBase.Type
                               .Where(s => s.Active 
                                       && subTypes.Intersect(s.SubType.Select(st => st.SubTypesID)).Any())
                                select new DropdownModel()
                                {
                                    ID = C.TypeID,
                                    Description = C.Name,
                                }).ToList());

        }
        return Types;
    }

HTH

Answer 2

You can write a similar join query to how you wrote your sql.

from C in DataBase.Type
join s in DataBase.SubType.Where(s => s.Active && s.SubTypeId == SubTypeIDINT) on C.TypeID equals s.TypeID
select new DropdownModel()
    {
        ID = C.TypeID,
        Description = C.Name,
    }