locked
Getting data while inserting with LINQ? RRS feed

  • Question

  • Hello,

    I need to do a couple of things with my method. I have a list of PersonID's and to them i want to add an AddressID. So when I insert the first new person to the database I want to get the AddressID and after that I need to aply this AddressID to all other Persons which I have in a list stored. Any suggestions how to do this within LINQ? Are it all seperated actions or are there advanced techniques to do this?

    Greetings,

    Spacelama

    Wednesday, April 25, 2012 8:27 PM

Answers

  • Space Lama,

    Linq itself is an expression language to query collections. 

    However, it is often also named as Linq to SQL and Linq to Ententities, for which a wizard is created which create a bunch of designer generated code to handle the database with many methods which are not even Linq

    One of the Microsoft forums for that is 

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/threads

    I (and probably more who know where they talk about) have no idea what you are using. It can even be datasets which you handle with Linq or a mapped solution for which you do the same.


    Success
    Cor

    • Marked as answer by Lie You Wednesday, May 9, 2012 2:55 AM
    Friday, April 27, 2012 6:41 AM

All replies

  • On 4/25/2012 4:27 PM, SpaceLama wrote:
    > Hello,
    >
    > I need to do a couple of things with my method. I have a list of
    > PersonID's and to them i want to add an AddressID. So when I insert the
    > first new person to the database I want to get the AddressID and after
    > that I need to aply this AddressID to all other Persons which I have in
    > a list stored. Any suggestions how to do this within LINQ? Are it all
    > seperated actions or are there advanced techniques to do this?
    >
    > Greetings,
    >
    > Spacelama
    >
     
    The Person record should be inserted first and all Address records
    should be linked to the Person by PersonID as the Address record or
    records are inserted.
     
    There is no such thing as you have some existing Address records that
    you need to link to a Person record on some kind of retrieval process
    after the Person is added.
     
    It should all happen at once Person inserted and Address records
    inserted linked to the Person.
     
    Wednesday, April 25, 2012 8:38 PM
  • I needed to read your message twice, but to be more clear:

    I need to insert 1 person. Get his AddressID by a new query. Then Aply this AddressID to the other Persons!?

    If I add a person to the database I cant say give me the last added personID with LINQ? I Should keep the user data (firstname/lastname and birthday) to do a new query and add an Address?

    Since a short time I have made the Person and the Address table connected with a foreignh key. What should I think of when inserting data with this new future?

    Greetings,

    Spacelama 

    Thursday, April 26, 2012 5:02 AM
  • On 4/26/2012 1:02 AM, SpaceLama wrote:
    > I needed to read your message twice, but to be more clear:
    >
    > I need to insert 1 person. Get his AddressID by a new query. Then Aply
    > this AddressID to the other Persons!?
     
    No that's not correct. You add a Person first, get its ID, you apply it
    to a Address record tying the Address record (the child) to its Parent
    record. You don't care about the AddressID
    >
    > If I add a person to the database I cant say give me the last added
    > personID with LINQ?
     
    Yes you can that's if you are using Linq-2-SQL or ADO.NET Entity
    Framework. You can get the PersonID right after you insert the Person
    object, because the object will have the PersonID property of the Person
    object populated after the insert. You can address the Person.PersonID
    right after insert and get the new ID assigned to the PersonID property.
     
    > I Should keep the user data (firstname/lastname and
    > birthday) to do a new query and add an Address?
     
    There should be no Address record. You can't add an Address record until
    you add the Person to get its PersonID to be applied as a foreign-key to
    the Address record before it's inserted. You can't insert an Address
    record (the child) without the Person (the parent) being inserted first.
    That's if you have the database table foreign-key constraints setup
    properly.
     
    >
    > Since a short time I have made the Person and the Address table
    > connected with a foreignh key. What should I think of when inserting
    > data with this new future?
    >
    What are you using? Are you using Linq-2-SQL, ADO.NET Entity Framework
    or what?
     
    Thursday, April 26, 2012 5:08 PM
  • Hello,

    Thanks for the reply. Now I need to figure out how to do all that. :p

    I am using Ling 2 SQL. I have an mdf file which contains the DB and I have an dbml file that gives me the oppurtunity to use Linq 2 sql.

    I have set the person table to child and the address table to parent. The other way I couldnt do, because it said that the parent cant be nullable if the child aint. Or something...

    Greetings,

    Spacelama

    Thursday, April 26, 2012 6:59 PM
  • On 4/26/2012 2:59 PM, SpaceLama wrote:
    > Hello,
    >
    > Thanks for the reply. Now I need to figure out how to do all that. :p
    >
    > I am using Ling 2 SQL. I have an mdf file which contains the DB and I
    > have an dbml file that gives me the oppurtunity to use Linq 2 sql.
    >
    > I have set the person table to child and the address table to parent.
    > The other way I couldnt do, because it said that the parent cant be
    > nullable if the child aint. Or something...
    >
     
    See, this where you are stuck and you don't know what to do.
     AddressBook  object -- Parent
     
    Person object -- a child object to an AddressBook object but a  parent
    to Address object -- multiple Person objects linked to an AddressBook object
     
    Address object -- a child to a Person object there can multiple Address
    objects linked to a Person object
     So here is the object you have to address in the add and it must all
    happen at onetime
     
    AddressBook.Persons.Addresses   -- this is the object you are dealing with.
     var ab = new AddressBook();
     
    ab.AddressbookName = "Help me";
     
    // setting all the properties the Addressbook object
     
    var person = new Person();
     
    person.FirstName = "Help Me1";
     // play close attention here
     
    ab.Person.Add(person);
     
    The Person object within the AddressBook is a collection of Person
    objects within the AddressBook object.
    -----------------------------------------------------
     So lets do this.
     
    var person = new Person();
     
    Your person object has been populated already.
     person.FirstName = "Help Me1";
     
    //play close attention here.
     var address ad = new Address();
     
    address.Address1 = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
     
    person.Address.Add(address);
     
    The Address object within the Person object is a collection of Address
    objects within the Person object.
     -----------------------------------------------------
     So it's this to keep it simple.
     
    var addressbook = new AddressBook();
     
    populate addressbook properties but do nothing with the Person
    <collection> property.
     
    var person = new Person()
     
    populate person properties but do nothing with Address <collection>
    property.
     
    var address = new Address();
     
    populate the address properties
     
    person.Address.Add(address)
     
    var address1 = new Address();
     
    populate the address1 properties
     
    person.Address.Add(address1);
     
    addressbook.Person.Add(person);
     
    SaveChanges();
     
    ---------------------------------------------------------
     
    Linq-2-SQL is going to save
     
    AddressBook.Persons.Addresses
     
    1) AddressBook object with 1 Person object with Person object having
    multiple Address objects.
     
    Linq-2-SQL ties it all together for you and populate foreign-keys by
    itself.
     
    That's what Linq-2-SQL does.
     What you need is a good book on Linq-2-SQL that's what you need that
    will show you what you need to know about Linq and Linq-2-SQL.
     
     
    Thursday, April 26, 2012 10:08 PM
  • Thanks for the big explanation. I haven't tried to code it yet, because I dont have time now, but I would like to react on some text that you wrote at this moment.

    You said:

    Address object -- a child to a Person object there can multiple Address

    objects linked to a Person object

    My tables looks like this:

    AddressBook

    Persons -> PersonID, FirstName, LastName, etc., AddressID

    Address -> AddressID, Street, HouseNumber, etc.

    You say that one person can have multiple addressess, but my table is more designed to assign an address to multiple persons. It is just for personal usage. So if i have a family and select all persons and then I want to add that singe AddressID to all persons. So... am I designen my tables wrong? Or am I not understanding it....

    Edit:

    Eventually I wanna make another table: Profiles.

    Profiles -> e-mail, mobile number, linkedin, facebook, etc.

    I cant have multiple profiles on 1 person. So this means that I should implement the PersonID to the Profiles table as a property identifier?

    Greetings,

    SpaceLama


    • Edited by SpaceLama Friday, April 27, 2012 6:38 AM
    Friday, April 27, 2012 6:35 AM
  • Space Lama,

    Linq itself is an expression language to query collections. 

    However, it is often also named as Linq to SQL and Linq to Ententities, for which a wizard is created which create a bunch of designer generated code to handle the database with many methods which are not even Linq

    One of the Microsoft forums for that is 

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/threads

    I (and probably more who know where they talk about) have no idea what you are using. It can even be datasets which you handle with Linq or a mapped solution for which you do the same.


    Success
    Cor

    • Marked as answer by Lie You Wednesday, May 9, 2012 2:55 AM
    Friday, April 27, 2012 6:41 AM
  • Oke, then I am gladly to tell u how I have made my DB. And if it aint clear after my post. Please let me know how to find out what kind of DB I have.

    At the moment I am using a .mdf file. So this means it is a SQL database ? Or is it an mapped database that is similar to the SQL Server Databases?

    Second I created an dbml file. I dragged both tables onto it. Then I selected the Person table and added a foreign key relationship. I also tried to apply this within the mdf file to select a table and add this relationshop, but when I dragged my tables into the dbml file i didnt saw a relationship. So I have a foreign key relationship made in the dbml and actually it is called an association, but it looks the same.

    When I hoover over the association i see the following:

    Address.Person -> Person

    Address <- Person.Address

    So this means the parent class is Address and child class is Peson and both have the properties of AddressID.

    I even got a other association, but I actually dont know how it got there. Maybe it was generated when I made the table... the association looks like this:

    Address.Address -> Address

    Address <- Address.Address1

    That's that about the table generation and relationship part. After that I created two stored procedures:
    InsertPerson - parameters: firstname, lastname, addressID

    InsertAddress - parameters: street, housenumber, etc

    Is it now more clear what kind of database I have?

    Greetings,

    Spacelama

    Friday, April 27, 2012 12:42 PM