How insert records on certain criteria? RRS feed

  • Question

  • I'm very new to LINQ to SQL. I have the following table:

    photoID    int    Unchecked
    albumID    int    Unchecked
    fileName    varchar(50)    Unchecked
    filePath    varchar(50)    Unchecked
    caption    varchar(50)    Checked
    profilePhoto    bit    Unchecked
    description    varchar(200)    Checked
    position    int    Checked
    certify    bit    Unchecked
    dateInsert    smalldatetime    Unchecked

    First scenario:
    Before I can insert the value true or false to field profilePhoto, I like to know if this the first record. If it is, set this profilePhoto field to true. If not, set the profilePhoto to false.

    Second scenario:
    Let's say a user has the following images in the table:

    Image1 profilePhoto=false
    Image2 profilePhoto=false
    Image3 profilePhoto=true
    Image4 profilePhoto=false
    Image5 profilePhoto=false

    One day he logs into the site and change Image2 as his profilePhoto=true. How do I make sure that all other profilePhoto besides the one he just changed is set to false when the changes are submit to the database?

    Third scenario:
    I have the following position or order for each of the images below:

    Image1 position=1
    Image2 position=2
    Image3 position=3
    Image4 position=4
    Image5 position=5

    If the user decides to delete Image2, is it relatively easy to automatically shift all the positions up like this:

    Image1 position=1
    Image3 position=2
    Image4 position=3
    Image5 position=4

    Fourth scenario:
    How do I get the last position/order and increment by one for any future image insertion to the database?

    Many thanks in advance.
    Thursday, November 5, 2009 4:58 PM


  • Hi Chucky,


    First scenario:

    You can use Count() method in LINQ. If it is zero, you can set profilePhoto to true.


    For example,



    Second scenario:

    For the performance reason, you can store the only true record’s id somewhere. When you set another record’s profilePhoto to true, you can update the old record.

    Or you’ll have to find the record like this,

    var q = (from p in context.Yourtable

    where p.profilePhoto == true

    select p).FirstOrDefault();

    q. profilePhoto = false;


    Third scenario:

    I’m not very sure what do you mean position. If you mean in the DB, the answer is yes. (Be careful about the profilePhoto field while deleting.)


    Fourth scenario:

    If you want to get the last record you can use Last() method.

    For example,

    var q = context.Yourtable.LastOrDefault();



    Best Regards

    Yichun Feng

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Yichun_Feng Thursday, November 12, 2009 2:24 AM
    Monday, November 9, 2009 5:00 AM