Solved

Combining LINQ to SQL with Inline SQL

Posted on 2008-06-13
5
1,392 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now