locked
linq to sql - association - one to one RRS feed

  • Question

  • User-929021816 posted

    Hello,

    Using linq to sql... I have commonly used assocations to make the one-to-many relation for the very common one-to-many scenarios. Currently I have the form of master-detail scenario where the 'detail' will always only be one related record, never 'many'. Essentially there is a main table and several different detail tables, one for each major type of element stored in the main table. Best illustrated by example data:

    tracking assets like computers, printers, firewalls, etc.. all of these share common field like manufacturer, model number, serial number, asset tag, etc... but then each type of item may have very different details, like cpu, ram, and hard drive size for computers, but only computers, not printers or firewalls etc.. so I have a main 'assets' table, and various detail tables like asset_ServerDetails and asset_FirewallDetails. A detail table will only ever have one record relating to the main 'assets' table, and vice versa.

    I can work with this doing the exact same thing I do for one-to-many scenarios, creating the assocation with the 'one' side being parent and the 'many' side being child. Since I know there will only ever be one related record, I can just append the .Single() method to my linq queries to get at the info I need, BUT, I'm wondering if that is what I should be doing? is there a more specific association setup that should be done for one-to-one related tables?

    e.g, this is what I do now:

    var asset = (from a in dc.Assets
                            where a.AssetID == AssetID
                            select a).First();
    
    sReturnValue = asset.Assets_Server_Details.First().PrimaryIPAddress;

    grabs the specific asset I need, then uses the association property to get at the related table, and uses First() method to get to record level so the fields can be accessed. Is this how a one-to-one relation should be handled with linq to sql? 

    It works and it's fine with me, no complaints here, I just want to know if I should be doing it a different way?? 

    Thursday, August 18, 2011 4:05 PM

Answers

  • User3866881 posted

    Hello c0Pe:)

    You mean that you know the relationship is One-to-One, so you want to return a single instance instead of returning IEnumerable collection and manually use First()?

    If yes, try this way——

    1) Execute the sql script:

    use master
    if exists(select name from sysdatabases where [name]='dbtest')
    drop database dbtest
    go

    create database dbtest
    go

    use dbtest
    create table tbUser
    (
       id int primary key identity(1,1),
       username varchar(max)
    )
    create table tbComputer
    (
       id int primary key foreign key references tbUser(id),
       computename varchar(max)
    )

    insert into tbUser(username)values('A')
    insert into tbComputer values(1,'ComputerA')

    2) Drag and drop from server explorar in VS2010 to generate db, this is one-to-one.

      using (DataClasses1DataContext dc = new DataClasses1DataContext())
                {
                    dc.tbUsers.First().tbComputer
                    }
    Here you can see that: tbComputer is only an instance instead of IEnumerable<tbComputer>
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 19, 2011 11:20 PM
  • User3866881 posted

    Yes, it's not associations but the databases' diagrams. You should know that if you want to create One-To-One relationship, you must link two primary keys together as the relationship.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 21, 2011 9:12 PM

All replies

  • User-451260051 posted

    I tend to shy away from using .First or .Single in favor of .FirstOrDefault and .SingleOrDefault.  The reason being, if your LINQ statement finds nothing, .First will throw an exception.  Using .FirstOrDefault, if the LINQ statement finds nothing, it returns null (the default).  Then you can test the result to ensure its not null before using it, in your case:

    var asset = (from a in dc.Assets
                            where a.AssetID == AssetID
                            select a).FirstOrDefault();

    if (asset != null)
    {
         sReturnValue = asset.Assets_Server_Details.First().PrimaryIPAddress;
    }

    Thursday, August 18, 2011 4:19 PM
  • User-929021816 posted

    thanks for the response tehremo, I appreciate it.

    For my particular case the *OrDefault methods shouldn't be necessary, only because I know the place/time I'm doing this in the app there would always be a record, if not, I would have big problems, hehe.  But your advice is excellent none the less and I will use your suggestion other places... 

    I would love to hear your response to the core question of the post though? Your info is excellent advice, but is more a side note than a reponse to the core question.

    Thanks again and I would love to hear from you further.

    Thursday, August 18, 2011 4:52 PM
  • User3866881 posted

    Hello c0Pe:)

    You mean that you know the relationship is One-to-One, so you want to return a single instance instead of returning IEnumerable collection and manually use First()?

    If yes, try this way——

    1) Execute the sql script:

    use master
    if exists(select name from sysdatabases where [name]='dbtest')
    drop database dbtest
    go

    create database dbtest
    go

    use dbtest
    create table tbUser
    (
       id int primary key identity(1,1),
       username varchar(max)
    )
    create table tbComputer
    (
       id int primary key foreign key references tbUser(id),
       computename varchar(max)
    )

    insert into tbUser(username)values('A')
    insert into tbComputer values(1,'ComputerA')

    2) Drag and drop from server explorar in VS2010 to generate db, this is one-to-one.

      using (DataClasses1DataContext dc = new DataClasses1DataContext())
                {
                    dc.tbUsers.First().tbComputer
                    }
    Here you can see that: tbComputer is only an instance instead of IEnumerable<tbComputer>
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 19, 2011 11:20 PM
  • User-929021816 posted

    Hello Decker,

    thanks for responding. I'm not absolutely sure I follow correctly, but I think you are saying the fix to my situation is a change in how I defined the tables in SQL server, not in the linq to sql classes associations... if I create my tables in this 'one to one' way then the linq to sql classes would operate accordingly. Is that correct?

    I typically just use sql management studio's gui for creating my tables, and pretty much just right click the column I want as primary key and choose 'set primary key'.. then in related table I do the same, so both have a primary key named 'AssetID' for example.

    If I understand you correctly, I need to make changes in my database, not my linq to sql classes, or their associations. Is that right?

    thanks again for the help, I appreciate it.

    Sunday, August 21, 2011 3:09 PM
  • User3866881 posted

    Yes, it's not associations but the databases' diagrams. You should know that if you want to create One-To-One relationship, you must link two primary keys together as the relationship.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 21, 2011 9:12 PM
  • User-929021816 posted

    ok, thanks Decker. I'll google on how to set that up using sql server management studio. My tables already exist but there is only test data in them, so if I have to delete and recreate I can, but of course I would rather be able to just make this change without doing that.

    thanks again.

    Monday, August 22, 2011 7:33 AM
  • User-929021816 posted

    wanted pop back in to say thanks again... I took the time to create all my relationships using sql server management studio, then in visual studio I deleted all my tables/association from the dbml file and the did the drag and drop of all my tables from server explorer again... and pow, all my classes AND the associations were all created for me, perfectly, beautiful! LINQ TO SQL is amazing! I love it. I started using it before Entity Framework came out and I only use MS SQL Server so I see NO reason for me to switch. LINQ TO  SQL is awesome... 

    thanks again for the help.

    Tuesday, August 23, 2011 2:18 PM
  • User3866881 posted

    Hello c0pe:)

    Glad to hear that and hope you do well with the technology!

    Thx again

    Tuesday, August 23, 2011 8:50 PM