Solved

Combining LINQ to SQL with Inline SQL

Posted on 2008-06-13
5
1,399 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
[X]
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
  • 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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

632 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