Monday, July 21, 2008

Video: Using SQL Functions in LINQ to SQL

Presenter:Mike Taulty

A large number of SQL server's standard functions (and functionality) are exposed through the functions that are callable from a LINQ to SQL query. In this session we'll look at a few examples of how this works.

The following text is a software generated transcript of the video. Click on a minute link to jump to a location within the video

Minute 0

Oh mice might hold you in working the developer and platform group in Microsoft in the UK and this is one of a series of short videos about a new technology that is coming with visual studio codenamed walkers that will be the next version of visual studio the technology is called into the sequel or language integrated query to sequel what that does it puts the new language integrated query pattern on top of the new object relational mapping API to data access to sequel server that this is one of a number of videos if you housing introductory session to session on the data context of the sessional mapping it may be worth or have a look at those before you pitch into the middle of the session here if I take a very quick look at how we can call functions within queries and easily I m thinking at the substandard functions are existing to sequel server hat that gets translated into how that works and just to give you a feeling of confidence about the way in which the screw is being executed on the sequels

Minute 1

Design him this was delivered in August I have the much technology preview that was the latest version available at a time of making the video and is likely to have so many of these sessions I can t create a very basic console applications into that console application I ve added a link to single diagram from a northern database with the customer table and the order table or acting more correctly the customers table in the order is terrible represented here on the diagram which gives me generated code let me programme against a data context and get hold of these things from the North and database so acknowledges them almost non existent bridges data context allows us to get hold of Northwind and entities and then you can start programme against them one of the most magical things that the links equal framework family it is that I consider us a pretty simple query is right queries such as we

Minute 2

Right to the console in the number of customers in the customers they also will go ahead and get context customers are you an extension method called Count Sir Twerton and do the ankle can come it will build that piece of code I ve got a sequel server trace running in the background so we can run this these of code we see that there are 92 records are not able but if you look at the sequel server trace within a radius I think I surprising in the first instance in a sequel was sent to sequel server in order to make that work that is what we will select Staff and customers we didn t own query though customers table and down go ahead history that back to the client account records back on crime we did the right thing in terms of the tea sequel that God generated a little further down as the matter of code and it will do little right to query so so query is from seen in context customers

Minute 3

Must select seed or country door length or something like that and a sofa reach into to get back another striker to the console is long run that there is the length of the countries that were really nice and the sequel server was difficult to trace from this was seen or as you d is calling the land function inside a sequel server women are even bringing those columns back to the client in order to do that work that s the something else what he wanted to bring back a page of data from somewhere in the customer s table such aimed at query here Allah and so from seeing customers lets you select seed customer ID but what if we don t want the whole tape would have wanted some section of the table in the middle wobbled it is will say three strange little access to riding again queried up

Minute 4

And what that is skipped a certain number of records let s skip the first 30 records and limits followed by taking the next 20 records for a writer of the console customer ID was built at a runner there are some customers from Northwind little outlook eluded outside the sequel server that is what the going is using in this case specifically sequel server 2005 capabilities such as road numbers and over in order to order things and then bringing back the top 20 results from the two generating a really smart query to sequel server 2005 we would have to use a different of signals at the 2000 in the framework supports that from this he shall got that we wrote the skin can take to we have these functions have been translated to reclaim obesity sequel for a hand is naturally by the framework

Minute 5

Looking again at more examples of things we can do so wanted to go to the customers table where for example it is discovered of that but so whereas the Sea Dart country but has he got contact name contains the string them a lesser something I analysis we find anyone has got that I would select customer ID there and we won t skip here any more because we might not find any records as front runner that must build that up to select from query there is really contains method on system and here is a runner against database with a couple of records that there had eluded over the sequel server weasellike clause s translating methods and system strange in

Minute 6

The like close the sequence of let us also will translation system or string that this will be the documentation for the August preview bug is nothing any idea as to how these things are being translated everything about mathematical functions may be much and that something like context orders maths and will take the order of the glass itself revalue or something like that that it was an action that column that run that we had a value out of it let s go ahead and have a look at sequel trace we can hopefully see that we selected the maximum that table at that point there is support from mapping between net daytime and timestamp function is not necessarily all of them but quite a number into what happens inside sequel server so if we want to try to query such as from or in context orders

Minute 7

Where let s go out and grab the order date and find a day of the week and I say where that was on a Tuesday but select older order ID from that in a little heaven in new racegoers and put them out so safe reach polling query consul arrive unavailable free run at query we can see that this without the Lord readies on the Tuesday and if you look at his sequel has been generated from that will see that what we do here is using date heart and convert in order to act city that grew inside the database whilst we get a think about it in terms of net types overhearing sign of code just a quick summary hits were a short video really the point is that it was to show that we can hold functions in England to sink or query and they turn into functions inside of sequel server to resort with the call string functions like

Minute 8

And Haenszel then they turned into functions and his sequel result with the golfing holiday time and they tended to functions into sequel aggregate functions that some counter max turned into the right function calls and his sequel and wealthy and see them because all malfunctions cosine and so on a Labour turn into the right function into sequel so and you got any functions inside of me to sequel queries especially something like an axle count you don t need to worry too much beyond the initial understanding that their framework is doing the right thing in sending the right function calls across the sequel server for you is not pulling that all the records to the client and then working out what the maximum was a bigger wrong way of going about it and that s not what the framework does listen pretty cool stuff to turn your functions in your queries into the right functions in the right queries inside of sequel so how do you go more red wine aired

Minute 9

And her aunt and a yen you are out there on screen it will provide information on the link project including an equal a great log out a linking action net and encourage it go have a look about you like reading and blocks and finally he loved life and feedback on a specific enjoyment fashion if we can mail me and I know that it is there on the screen to mail me tragic west London fashion other than and famously taken about 11

Click here to go to original video page

No comments: