How to iterate over results of a linq-to-sql query result and append the results?

Question

I'm new to C# and Linq-to-Sql.

I've a table 'InstrumentTypes' of this form:

typeId(int)  | type(varchar)  |  subttype(varchar)

101               Keys           keyboard
102               Keys           accessories
103               Guitar         acoustic
104               Guitar         electric

I need to fetch all 'typeId's from the table based on a search by 'type' as input, and all the typeId's needed to be bound to a ASP Repeater.

So far I've written the following code:

// requestType contains the type from the search
var type = (from m in database.InstrumentTypes
            where m.type == requestType
            select m);
foreach(var typeId in type)
{
    //code
}

I'm unable to figure out how to iterate over the results from the query, store them in a datastructure and bind them to a Repeater.

The following code binds it to the Repeater:

Repeater1.DataSource= //name of data structure used to store the types goes here
Repeater1.DataBind();

Could anyone please help me out?

EDIT: For each typeID obtained, I want to access another table 'Instruments' and retrieve all Instruments belonging to that typeId. The table 'Instruments' is like this:

instrumentId     typeID    name     description
1000             101       yamaha   xyz

Based on Arialdo's answer, I'm doing this:

var type = (from m in database.InstrumentTypes
                          where m.type == requestType
                          select m);
            var instruments = new List<Instrument>();
            foreach (var i in type)
            {
                instruments.Add(from x in database.Instruments
                                where x.typeId == i.typeId
                                select x);
            }
            Repeater1.DataSource = instruments;
            Repeater1.DataBind();

But I get a compilation error saying 'The best overloaded method match for the List has some invalid arguments'. Where am I going wrong?

Solution 1

What you get from

var type = (from m in database.InstrumentTypes
        where m.type == requestType
        select m);

is a collection of InstrumentTypes, not a collection of ids.

This works for me

var types = (from m in database.InstrumentTypes
        where m.type == requestType
        select m);
var ids = new List<int>();
foreach (var type in types)
{
    ids.Add(type.Id);
}

which you could easily convert to

var ids = (from m in database.InstrumentTypes
        where m.type == requestType
        select m.Id).ToList();

[Edit]

You can directly query your instruments and navigate to related objects, as long as you defined a relationship between InstrumentType and Instrument.

var instruments = (from i in database.Instrument
                      where i.InstrumentType.type == requestType
                      select i);

No need to have separate foreaches or queries. The i.InstrumentType will convert to a join, as you could verify with a SQL profiler

Solution 2

I am not sure what you are asking.

With out explicitly defining the returned type of your query you are already returning an IEnumerable<InstrumentTypes> object. If you want a list of ID's you can simply refine your query to return ID's rather than a list of InstrumentTypes. Of course, then you'd be returning an IEnumerable<int> object.

var type = (from m in database.InstrumentTypes
        where m.type == requestType
        select m.typeId);