CODEWITHSUNDEEP
c#asp.netsql-serverlinq
109221793
109221793

Reputation: 16897

Help with Linq query

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 :)

Upvotes: 3

Views: 101

Answers (3)

PCasagrande
PCasagrande

Reputation: 5412

The only way I see it being possible is if there is a one to one relationship connecting Games and GameData tables. If Game.GameDataID can only use a GameDataID once (and only once) then this should work. If a GameData record can be used by multiple Games then this method will not be usable.

gf = (from t in ctn.GameFormat
     join g in ctn.Game on t.Game equals gd.GameDataID
     where t.GameFormatProductcode == gameFormatProductCode
     select g.GameID).FirstOrDefault();

Upvotes: 3

Henric
Henric

Reputation: 1410

The GameDataId from GameFormat could be used to find the GameID from the Game table.

EDIT: Way too slow! Good answer by Geoff.

Upvotes: 1

Geoff Appleford
Geoff Appleford

Reputation: 18832

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;

}

Upvotes: 5

Related Questions