Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1406
  • Last Modified:

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
0
used2could
Asked:
used2could
  • 2
  • 2
2 Solutions
 
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
 
CarlVerretCommented:
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
 
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
 
naspinskiCommented:
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now