Help with Linq query

Question

I'm writing a linq query and I've run into trouble with it so I was wondering if someone could help. Here is a bit of background:

I didn't design the database so the structure cannot be changed. So I have the Main 'Game' table, which has a main product code, A foreign key in this table is GameDataID from the GameData table which contains information such as release dates, released by, etc. Then I have the GameFormat table, which contains the product codes for the game in each format, e.g. Mac, Windows, etc and again the GameDataID is a foreign key. See Below.

Game

GameID PK
MainGameProductCode
MainGameTitle
GameDataID FK

GameData

GameDataID PK
GameReleaseDate
GameReleasedBy

GameFormat

GameFormatID PK
GameDataID FK
GameFormatProductcode

So when sales reports are received back, some only contain 'GameFormatProductCode' as the product identifier. So from 'GameFormatProductCode' I need to retrieve the 'GameID' in the main Game table.

So far I have written the linq query to retrieve the GameFormatProductcode from the GameFormat table, however I am unsure how to go about retrieving the GameID from the main Game table.

private Int64 GetGameID(string gameFormatProductCode)
{
    ModelCtn ctn = new ModelCtn();
    Game game = null;
    GameFormat gf = null;

    gf = (from t in ctn.GameFormat
        where t.GameFormatProductcode == gameFormatProductCode
        select t).FirstOrDefault();

    // Need to find GameID from Game table and return it.

    return gf;

}

Any linq experts out there care to point me in the right direction? Very new to Linq so be gentle :)

Answer 1

You can get the GameDataID from the GameFormat table and use it to query the Game table

private Int64 GetGameID(string gameFormatProductCode)
{
    ModelCtn ctn = new ModelCtn();
    Game game = null;
    GameFormat gf = null;

    gf = (from t in ctn.GameFormat
        where t.GameFormatProductcode == gameFormatProductCode
        select t).FirstOrDefault();

    // Need to find GameID from Game table and return it.
    var gID = (from t in ctn.Game
               where t.GameDataID == gf.GameDataID
               select t.GameID).FirstOrDefault();

    return gID;

}