How insert records on certain criteria?

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.

Answer 1

Hi Chucky,

 

First scenario:

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

 

For example,

context.Yourtable.Count()

 

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.