Combining LINQ to SQL with Inline SQL

I am converting a project that i am in the process of developing to use LINQ to SQL. My problem is a scheduled process that syncs data with a 3rd party via several XML files. I am building one huge sql script to run at the end of the xml parsing process  to then execute in one trip to the database. A big part of this is a series of SQL "if" statements to see if something exists with some conditional logic and either update or insert a new record. From what i know LINQ doesn't handle conditional update or inserting without multiple database trips. I want to be able to get the generated SQL (select/insert/update/delete) using LINQ (without a trip to the DB) then mix it in with my own SQL for the conditional decision of insert or update. How do i get the generated SQL from LINQ for a select, insert, update or delete ?

Thanks
~B
used2couldAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
CarlVerretConnect With a Mentor Commented:
Hi,

Another useful way to look at the generated SQL statements is to provide your db an output for the log property ex;

db.log = console.out

I think you can even output the results to a stream.

For the rest, I would suggest you to make it in two major queries :
first, update existing persons' badges where id exists
second, insert everything else...
0
 
naspinskiCommented:
You can get the generated SQL from LINQ by running it in debug mode, then simple hovering over the variables in the view window.  Scott Guthrie has a great video showing exactly what I mean.  I think it is this one:
http://weblogs.asp.net/scottgu/archive/2007/01/28/video-using-linq-with-asp-net-in-vs-orcas-part-1.aspx

But I think you might be able to get all the info you need in one trip, then run through it to sort, then insert it through linq if you want.  I am not sure if I completely understand.
0
 
used2couldAuthor Commented:
Thanks for the reply nasp.
Using the debugger to get the SQL hits the database each time you view it. Itested this with SQL Profiler.

Here is an example in SQL of what i need to in LINQ:
Declare @PersonId int,
Set @PersonId = 0;
Select @PersonId = PersonId from Persons where BadgeNumber = 'XXXXXXXXXXX';
IF @PersonId > 0
  Begin
     Update Persons Set Name = 'John', BadgeNumber = 'XXXXXXXXXXX'' Where PersonId = @PersonId
  End
Else
  Begin
     Insert into Persons (Name, BadgedNumber) values('John', 'XXXXXXXXXXX'');
     Select @PersonId  = Scope_Identity();
  End

I would loop and parse through lots of raw information to construct several of these types of statements into one SQL script and submit it to the database.
0
 
used2couldAuthor Commented:
Using a stream for the Log won't help because it has to hit the database before it outputs the SQL. I like the idea of running updates first then inserts but that wouldn't work because there are extra steps that involve child items of Person. LINQ does multiple calls when inserting Parent > Child objects to get the newly created ID for of the parent.
Its starting to look like i will have to write an extension to build simple selects/updates/inserts using reflection on the attached object :(

The new concept of querying collections via LINQ if awesome but i think LINQ to SQL differently doesn't live up to the hype.
0
 
naspinskiConnect With a Mentor Commented:
LINQ is great for some situations, but not all. Just another tool to throw in the toolbox for when it's appropriate.  Which it sounds like this might not be one of those times.
0
All Courses

From novice to tech pro — start learning today.