How to use IF and THEN statements in LINQ?

  • (3 Pages)
  • +
  • 1
  • 2
  • 3

36 Replies - 2495 Views - Last Post: 30 June 2016 - 04:31 AM Rate Topic: -----

#1 JapanDave   User is offline

  • D.I.C Regular

Reputation: 35
  • View blog
  • Posts: 413
  • Joined: 01-February 16

How to use IF and THEN statements in LINQ?

Posted 19 June 2016 - 09:14 PM

How do you use if then in LINQ queries?

I want to be able to test 2 columns in a table and then have data inserted when the criteria is met.

ie, I have a LINQ query that gets staff data from a date column and Staff number column, then I check two columns to see if they are null values. If they are, the query then returns a value and in a foreach loop I can go about putting data into the the column that needs it,

My Query,
var exampleQuery = sql.Staff_Time_TBLs.Where(a => a.Date_Data == day && 
                                   a.Staff_No == SelectedEmployee.Key && 
                                   a.Time_Data_1 == null && 
                                   a.Time_Data_2 == null)
                                   .ToList(); 



I have no clue how to even start to do this ,but this is what I was thinking. Obviously not evn close to correct syntax.

var exampleQuery = sql.Staff_Time_TBLs.Where(a => a.Date_Data == day && 
                  a.Staff_No == SelectedEmployee.Key && 
                  if a.Time_Data_1 == null && a.Time_Data_2 == null then a.Data_Col = "Some Value");
                                   


Any advice would be great.

This post has been edited by JapanDave: 19 June 2016 - 09:15 PM


Is This A Good Question/Topic? 0
  • +

Replies To: How to use IF and THEN statements in LINQ?

#2 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 7447
  • View blog
  • Posts: 25,085
  • Joined: 05-May 12

Re: How to use IF and THEN statements in LINQ?

Posted 19 June 2016 - 09:26 PM

Looks to me that you want to do an update rather than a query.
Was This Post Helpful? 1
  • +
  • -

#3 JapanDave   User is offline

  • D.I.C Regular

Reputation: 35
  • View blog
  • Posts: 413
  • Joined: 01-February 16

Re: How to use IF and THEN statements in LINQ?

Posted 19 June 2016 - 09:28 PM

Hey Skydiver, yes, I want to get out of using a foreach loop as it takes too much time.

Cheers
Was This Post Helpful? 0
  • +
  • -

#4 JapanDave   User is offline

  • D.I.C Regular

Reputation: 35
  • View blog
  • Posts: 413
  • Joined: 01-February 16

Re: How to use IF and THEN statements in LINQ?

Posted 20 June 2016 - 08:33 AM

Skydiver, anyone, could you just poke me in the right direction, at present, I have all sorts of different code and not even get close to making anything that can even be compiled.
Was This Post Helpful? 0
  • +
  • -

#5 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5106
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: How to use IF and THEN statements in LINQ?

Posted 20 June 2016 - 09:19 AM

Well, unfortunately you can't do exactly what you want to do there. The Where extension method is for filtering. Don't try to make it do something it's not designed for (updating/changing data).

One of the biggest advantages of LINQ is that it is fluent and composable. You can "chain" method calls together. So, you can use your Where function to reduce the dataset down to what needs updating, then use the Select function:

var results = sql.Table.Where(p => p.SomeCriteria == "some criteria")
					   .Select(p => {
							p.SomeField = "some value";
							return p;
					   });


Of course, you want this to be efficient. But it's not really going to be any more efficient than writing a loop to do the update. Where and Select are both implemented as loops. In this case, your Where function is being translated to SQL, but the Select will still be a loop. You're not really gaining anything by doing that instead of a foreach.
Was This Post Helpful? 1
  • +
  • -

#6 JapanDave   User is offline

  • D.I.C Regular

Reputation: 35
  • View blog
  • Posts: 413
  • Joined: 01-February 16

Re: How to use IF and THEN statements in LINQ?

Posted 20 June 2016 - 04:27 PM

OK, I see what is going on. I am still to learn more about LINQ queries. Thanks for your help.

Dave
Was This Post Helpful? 0
  • +
  • -

#7 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 7447
  • View blog
  • Posts: 25,085
  • Joined: 05-May 12

Re: How to use IF and THEN statements in LINQ?

Posted 20 June 2016 - 05:23 PM

I think I was much too subtle. Instead on using LINQ to get rows to edit, I was hinting that it maybe more efficient to let the server do all the work by using the SQL UPDATE statement. Something like UPDATE Staff SET Data="Some Value" WHERE Date1 IS NULL AND Date2 IS NULL.

I know that you were asking about LINQ, but when you have a hammer, not everything is a nail.
Was This Post Helpful? 0
  • +
  • -

#8 JapanDave   User is offline

  • D.I.C Regular

Reputation: 35
  • View blog
  • Posts: 413
  • Joined: 01-February 16

Re: How to use IF and THEN statements in LINQ?

Posted 20 June 2016 - 06:11 PM

@Skydiver, maybe something like this?

UPDATE Staff_Time_TBL SET Info_Data = 'stuff' 
                      WHERE Staff_No = 3201 
                      AND Time_Data_1 IS NOT NULL 
                      AND Time_Data_1 IS NOT NULL 
                      AND Date_Data BETWEEN '2016/3/16' AND '2016/4/16' 


Unfortunately this is not correct syntax.
Was This Post Helpful? 0
  • +
  • -

#9 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 7447
  • View blog
  • Posts: 25,085
  • Joined: 05-May 12

Re: How to use IF and THEN statements in LINQ?

Posted 20 June 2016 - 06:19 PM

What is the exact error that SQL gives you?
Was This Post Helpful? 0
  • +
  • -

#10 JapanDave   User is offline

  • D.I.C Regular

Reputation: 35
  • View blog
  • Posts: 413
  • Joined: 01-February 16

Re: How to use IF and THEN statements in LINQ?

Posted 20 June 2016 - 06:30 PM

In SQL Server manager running a query directly to the server from there, I was getting an error near "Staff_No = 3201",

But now it is working????

I must admit I first ran it like this,

UPDATE Staff_Time_TBL SET Info_Data = 'stuff' WHERE Staff_No = 3201 AND Time_Data_1 IS NOT NULL AND Time_Data_1 IS NOT NULL AND Date_Data BETWEEN '2016/3/16' AND '2016/4/16'


And then changed only to using mutiple lines, the code is otherwise exactly the same,
This worked.
UPDATE Staff_Time_TBL SET Info_Data = 'stuff' WHERE Staff_No = 3201 
AND Time_Data_1 IS NOT NULL 
AND Time_Data_1 IS NOT NULL 
AND Date_Data BETWEEN '2016/3/16' AND '2016/4/16'



Why would this happen? Is it bad practice to have everything on one line?
Was This Post Helpful? 0
  • +
  • -

#11 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 7447
  • View blog
  • Posts: 25,085
  • Joined: 05-May 12

Re: How to use IF and THEN statements in LINQ?

Posted 20 June 2016 - 06:59 PM

You may find this ironic given that I had suggested using SQL, but I am actually, anti-SQL. I have many personal biased reasons, and the last piece of odd behavior you noted above just added to my list.

It should have acted consistently whether you had everything on a single line, or on multiple lines. Hopefully a more SQL savvy member of this forum can shed more light on why it failed with single line, but succeeded multi-line.

As an aside, in your OP, you were checking for equality to null, but now your SQL is checking for not null? Why?
Was This Post Helpful? 0
  • +
  • -

#12 JapanDave   User is offline

  • D.I.C Regular

Reputation: 35
  • View blog
  • Posts: 413
  • Joined: 01-February 16

Re: How to use IF and THEN statements in LINQ?

Posted 20 June 2016 - 07:27 PM

Yes, I can see why you do. I know a enough to get me by, but I am still have trouble forming complex statements,

Strictly speaking, I actually have 4 LINQ queries that I need to run on every round of the loop and was hoping I could combine everything so I could just everything in one query.

The first query in the OP checks for nulls in those two columns, the second query checks if both of those are columns are not null. I have another that checks if one is a null and the other is not and vice versa. Each gives a different value to be updated in the server.

I was hoping I could join the 4 queries together? Now I know this is wrong as I get errors from the get go.
This is just an example of two queries together to represent what I mean.
UPDATE Staff_Time_TBL SET Info_Data = 'Data Input 1' WHERE Staff_No = 3201
AND Time_Data_1 IS NOT NULL
AND Time_Data_1 IS NOT NULL
AND Date_Data BETWEEN '2016/3/16' AND '2016/4/16' 
JOIN SET Info_Data = 'Data Input 2' WHERE Staff_No = 3201
AND Time_Data_1 IS NULL
AND Time_Data_1 IS NULL
AND Date_Data BETWEEN '2016/3/16' AND '2016/4/16'

This post has been edited by JapanDave: 20 June 2016 - 07:28 PM

Was This Post Helpful? 0
  • +
  • -

#13 JapanDave   User is offline

  • D.I.C Regular

Reputation: 35
  • View blog
  • Posts: 413
  • Joined: 01-February 16

Re: How to use IF and THEN statements in LINQ?

Posted 21 June 2016 - 01:41 AM

I think I have this sorted with a SQL CASE statement. Thanks for the help.
Was This Post Helpful? 0
  • +
  • -

#14 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: How to use IF and THEN statements in LINQ?

Posted 21 June 2016 - 07:55 AM

Just as an asside, lines 6 and 7 and lines 2 and 3 in your most recent post are exactly the same, one of those in each of those sets should be able to be removed. As for the multi line thing, I have no idea. I've had similar things happen where I was writing an IF statement with 1 line underneath it and it wouldn't work unless I had the long version of an if statement.
Was This Post Helpful? 0
  • +
  • -

#15 smendoza88   User is online

  • D.I.C Head

Reputation: 27
  • View blog
  • Posts: 128
  • Joined: 13-July 11

Re: How to use IF and THEN statements in LINQ?

Posted 21 June 2016 - 09:06 AM

Are you updating the column Info_Data with different data if it's NULL or NOT NULL?
Was This Post Helpful? 0
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3