Video: User Defined Functions in LINQ to SQL

Presenter:Mike Taulty

LINQ to SQL has facilities for exposing your own user defined functions to the framework whether those be scalar valued or table valued. In this session we'll take a look at how that 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 minus I told him with Microsoft in the UK and this is a short video about a new technology that is going with visual studio coating orchids that will be the next version of visual studio that follows on from 2005 the new technology fulfilling or language integrated query to sequel and what this does is it gives us the new link pattern that we see in various places in visual stew augurs over the top of a new object relational mapping API represents relational roles from sequels of the database tables has object in Henry VIII minute with a naturally programmes however say this is one of a series of sessions of the housing only the other sessions at it perhaps looking at some of the earliest sessions first particular the introduction and these sessions are mapping data contacts will be useful in this setup must look at is how we can call our own functions we ve written the database directly from them to sequel and had a framework

Minute 1

Is the right thing for it in terms of making that work in a session and working with a large technology preview of visual studio August this is the latest version available at the time of recording video update so here we discover simple console application and into Castle application is going to put a link to single diagram as we ve done in various other sessions scroll to the bottom this dialogue get them into single diagram got lost when and always wanted it here is bring onto a diagram p customers table from North winds that we can manipulate customers must as to their and everyone to look at how we can play with functions then we should go and build a function ourselves and we can then manipulate so that pop over to a seat was a window and just target any previous code with in their analyst of the very simple function but throughout and build our own streamlined function

Minute 2

For great fun general college turn empty struggle and it will take a stringed father as I shall let others involved maths our and wit that strain were going to return an integer and will just go ahead and say a semblance of strength to illustrate her function and history when it is now poor bugger with visual studio and it is a studio with their link to signal diagram the customer and piano that of a sober explorer and find within the server explorer functions we got within the database because it empty still and is here that Strang and he still ran onto the diagram with the representation of this if we close the diagram and go to have a look at the generated code will find the date

Minute 3

Context much like we don t restore procedures if you look to the various rental procedures we have a new method on this classical empty still and you can see apparent is that their attribute it to correspond to the parameters on function in the database you can see that without visitors to say it is a function from the point of view of link to sequel this is a function moment at the database and what this means is that we can go back to code and we can start a right travelled so that again and have an also dead contacts and from a contest we try to query such as a safer seen in the context of customers must go ahead and select and will use in our case context art empty still and are puzzled as a different way unless they were

Minute 4

Their context or NT Stallone and oil seed customer ID ears and Ashley Norton customer I didn t fancy look as they all tend to be around about five characters of a slave bigger or equal to five that select see and have reached one of those things that has gone writer s customer ID from the buzzword and run on the code I would not have got built there is little than I would seem that function NT Stallone the framework generated code at the moment in the preview looks to rid turn the wrong data tightly seemed of an object you and the framework code generated code must turn this into an integer must turn that into an integer less than that of villages if we can compile we can compile as far as it was adjacent to see what s going on here so it is an

Minute 5

Profiler and the greater trace the standard trace against the database are running that Alice Rumsfeld again let s go back run the code does customers coming back from the database that has a little adjacent to what actually got centre the database and in that particular case noticed that in the database ratified a statement which executed the function service in the database with a parameterised query so we did the right thing in terms of NBC sharp code when we call that escaped the rapid code 1 eagle empties still and here we aren t calling this back on the client in any fashion and that makes our visitors really exist on the client what ratty doing is capturing the whole query as we always do in its sequel and were turning this into an implication of that function over there in the sequel server

Minute 6

Working the way would expect the queries being written to call the function of the roads at the site are as part of the query now when recording function of this wave at the fundholding is defining a query until the enumerator query nothing will be sent to seek the server and with individual goalless function as you already know we call the service at the plans if you can call the function clients if you want to so we can get rid of this family to write out to the console of the lifestyle of some strange is an originally complex the anti struggle and will pass it some strange and if we manage to get that right and were making a trip across to the database in order to calculate the lands of something which is not enough is particularly useful think we can use this function and son of a query if that makes sense to do it now because it s it was over we can also have table valued functions so let s go ahead and ratify to look to the

Minute 7

A proposal to create a function and collects empty customers by country law passed the come true with his girlfriend Chard went in a something other is going to return the table and will just go out and so returned select the field as riding company name from customers wherever country is the country that are spent on a specific programme function up today and that let s go back to a code environment visual studio and will pop over to the design diagram that regard but also the explorer just refresh the list of functions regard his empty customers by country cry that stored procedure there onto a diagram on a new entry on honesty for empty customers like a

Minute 8

It takes the country has a strained because of the diagram of how the generated code that is just pop into generated code here and was seen that we have a new function now on data context is called NT customers by country and it were denizens of the generated data type in this case empty customers by country thing find that same system could certainly get there we can see that this is a class that maps to the shape of the data that comes back from function to receive customer ID and we see a company name on their as he wrote in a particular function now at the time of recording a video I m not entirely certain that the code that I m giving generated for why table function of here the one we just added empty customers by country is 100

Minute 9

Rhett only displaces out a little bit so when I reference Steve online documentation y Disley slightly different code from achieving the scenario and I know that if I progress and run this particular physical it seems to fail for such a one of those something missing there an echo gentoo orders just out of step with the blocks or what but I made it to replace this piece of code has been generated for me with a separate piece of code I was posting here as encircled the set of the documentation which means that the coat generation tool could certainly have generated it and expect that something will get sorted out their office in the next preview or as removal on a essentially this should I think it are degenerative for me and it turns into this works financially track and compile up bookseller Mr and S often typing that expression than an obvious type in pileup so we got know something the

Minute 10

Would have been spattered by the generation tool but fundamentally from a user perspective cause perspective we have with this method data contents of empty customers by country and every part back to cold we can then go and use out to try to query their sofas seen in context customers by country with the UK that selects see amethyst enumerates around those things also feature and he customers by country let colour see again in query console write line or were forgotten and we can arrive at the company name and address varieties such after the two things must build that is if we get success in running it works out it went faster and we can see that for those particular customers the in the UK we ve gone and evaluated function we ve gone and got

Minute 11

A table of data from a function that was the way that week we wrote the function to return a table with a thriving company name and refused it back in link to sequel just by calling a matter of the data context they go through and causes that function to be evaluated in sequel server and then we get that the results are expected to is using a cable value function really in the same way that we used a scale of valued function we bring it into the diagram into it on the command line tools we go ahead and that gives us extra method data context with all that method anecdotes and calls the right function inside a sickle cell late network just a quick summary here around what was seen with seeing that just like we saw a previous video where we could use built in sequels of the functions naturally from link to sequel and making user procedures naturalism is linked to sequel we can also use custom functions and we threaten from link to sequel

Minute 12

We can use the tools to bring those functions into maps environment that could be the command line tool like the designers are used here what happens that that is in with additional methods of data context that was naturally aroused callers functions at result we could very scale of valued functions would apply with table valued functions functions can have parameters Galloway will expect is how this works in the framework would bring them in abdomen and we just call them on that data context now where you go to board me not knowing there are enter on and the end the URL that their on screen it will provide you information on the link object including an equal at a weblogs are ad link in action net and encourages you have look about you like reading seven blocks and find they can t cry from feedback on specific enjoying the flesh and three to mail me on my inner vestige of their on screen two to mail me on plan and on fashion other than that and Ltd they could have lived in

Minute 13

Handhelds catch again in the future

Click here to go to original video page