Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Combining LINQ to SQL with Inline SQL

Posted on 2008-06-13
5
1,396 Views
Last Modified: 2013-12-17
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
Comment
Question by:used2could
  • 2
  • 2
5 Comments
 
LVL 21

Expert Comment

by:naspinski
ID: 21782356
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
 

Author Comment

by:used2could
ID: 21782534
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
 
LVL 3

Accepted Solution

by:
CarlVerret earned 250 total points
ID: 21783641
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
 

Author Comment

by:used2could
ID: 21785662
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
 
LVL 21

Assisted Solution

by:naspinski
naspinski earned 250 total points
ID: 21787774
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question