LINQtoSQL Error: Comparison operators not supported

I'm having a bit of a problem with the code below:

    var composer =
        (from comp in db.Composers
         where comp.Composer_Name.Contains(composerTextbox.Text)
         select comp.Composer_ID);

    var song =
        (from sng in db.Songs
        where sng.Composer_ID.Equals(composer)
         select sng.Track_ID);

    var track =
        (from trk in db.Tracks
        where trk.Track_ID.Equals(song)
         select trk.CD_ID);

    e.Result =
        from result in db.CDs
        where result.CD_ID.Equals(track)
         select new { result.CD_Title };

The error that pops up when I try to run the code is as follows:

    Comparison operators not supported for type 'System.Linq.IQueryable`1[System.Int16]'.

Here's how the Database is arranged:

• CDs
  • CD_ID
  • CD_Title
• Tracks
  • CD_ID
  • Track_ID
• Songs
  • 开发者_如何转开发Track_ID
  • Composer_ID
• Composers
  • Composer_ID
  • Composer_Name

My main goal in the code is to allow the user to search through the database for CDs that were composed by the person of their choice. Note that some CDs may have more than one composer, since the songs/tracks allow more than one entry in the Composer section.

What is supposed to be returned to the user is a list of CDs in a GridView.

Any help at all would be appreciated!

Edit:

As Mr. Kirk Woll said, what I'm actually trying to do is return a list of CDs against possibly many composer ids.

I've went ahead and updated the code like he suggested:

    var composers =
        (from comp in db.Composers
         where comp.Composer_Name.Contains(composerTextbox.Text)
         select comp.Composer_ID).ToArray();

    var song =
        (from sng in db.Songs
        where composers.Contains(sng.Composer_ID)
        select sng.Track_ID);

I tried the following to see if it would bring up a correct list of CDs:

    var composer =
        (from comp in db.Composers
         where comp.Composer_Name.Contains(composerTextbox.Text)
         select comp.Composer_ID).ToArray();

This is supposed to grab all of the valid Composer IDs and place them into an array.

    var song =
        (from sng in db.Songs
         where composer.Contains(sng.Composer_ID)
         select sng.Track_ID).ToArray();

This is supposed to look into the list of valid Composer IDs and make a new array of valid songs.

    var track =
        (from trk in db.Tracks
         where song.Contains(trk.Track_ID)
         select trk.CD_ID).ToArray();

This is supposed to look into the list of valid songs and make a new array of valid tracks.

    e.Result =
        from result in db.CDs
        where track.Contains(result.CD_ID)
        select new { result.CD_Title };

This is supposed to look into the list of valid tracks and extract all of the CD Titles associated with them.

I tried running the code, and there were no exceptions, but the only result that was returned in the search was the very first CD in the database. Any attempts to search for a new composer only returned the same CD.

This statement:

var composer =
    (from comp in db.Composers
     where comp.Composer_Name.Contains(composerTextbox.Text)
     select comp.Composer_ID);

Returns an IQueryable<short>. In other words, it could be rewritten as:

IQueryable<short> composer =
    (from comp in db.Composers
     where comp.Composer_Name.Contains(composerTextbox.Text)
     select comp.Composer_ID);

This line:

var song =
    (from sng in db.Songs
    where sng.Composer_ID.Equals(composer)
     select sng.Track_ID);

Tries to make a comparison between Composer_ID (presumably a short) and composer (of type IQueryable<short>). Clearly this won't work.

Probably your first statement should look like this:

var composer =
    (from comp in db.Composers
     where comp.Composer_Name.Contains(composerTextbox.Text)
     select comp.Composer_ID).FirstOrDefault();

Notice the .FirstOrDefault() that we added -- this makes sure that your query returns a single short value and ensures the comparison is between short and a short, so should fix the problem.

Potentially you really mean to compare against possibly many composer ids (i.e. all composers that have a name that contains the specified text), in which case it should be rewritten as:

var composers =
    (from comp in db.Composers
     where comp.Composer_Name.Contains(composerTextbox.Text)
     select comp.Composer_ID).ToArray();

Now we have an array of valid composer ids. We can rewrite the second part to query against that array:

var song =
    (from sng in db.Songs
    where composers.Contains(sng.Composer_ID)
    select sng.Track_ID);

This will return any songs that have a composer id contained in the preceding composers array of ids.