Check it out here
Google Search
Monday, March 24, 2008
Monday, March 3, 2008
When Do LINQ to SQL Queries Execute?
Overview
Defining a query and executing it are two different things in the LINQ to SQL framework and it's important to realise when queries get executed and under what conditions. Here we take a look.
(Duration: 8mins 49secs)
360ebff0-3a6d-428a-a28f-c9ec991964b4 0.01 I ll lies like Tauziat with Microsoft in the UK and this is one of a series of short videos about a new technology is coming with visual studio code 10.01 in August that technology is called into sequel and offers as the new language integrated query pattern on top of an object relational mapping API in the net framework version 20.01 2 5 editor is one of a series of sessions if you haven t seen the intro it like that looking at the intra session before you go into this 30.01 session here I does want to take a very short period of time just to look at how the queries that we build with link syntax are actually executed and 40.01 more specifically when there executed because living is very important so you get the right man to control and flexibility in your code okay here I am in visual studio 50.01 code in August the March technology preview which was the latest publicly available preview at the time of making this video and I ve set up a simple causal application seizure 60.01 As we did in the introductory session at Ghana added a link to sequel diagramming here and I ve dragged only the customer table from 70.01 North went on to that diagram in order that we can programme against so if you delighted excessively low that we can go ahead and 80.01 declare ourselves a data context will dominate one of those with a connection strength and you know that we can go and queried it so 90.01 we can say things like from seeing converts customers what we got it wrong already exist obvious that customers am so let s see it 100.01 is fine and we can do an unsafe reach customers see in that query has got a right seed customer ID to the console out 110.00999999999999 of dollars the previous sessions and got run that we get our customers from the North database and and that s really just getting this reversal of 120.01 Discussing the topic here now with only a small amount of code it s interesting to me to know what happens when you actually declare this query here 130.01 and what happens when the numerator query in the background that the sequel server profile address running and is currently empty and what is a single step is 140.01 coastal it was a 10 was single step in with great context we define our query anything that I really wanted to get from this session is the 150.01 point where we define this query we did not go to the database so all that happened and is through an interface is called by query will in 160.01 the framework is we have captured the definition of this query and you can all promising upbringing in Tallis until the debugger thinks that he can she see 170.01 and what that query looks like a an expression sense inside memory here the point of and there which the queries it is actually executed it here when the numerator 180.01 Some only first run enumerators think we ll see that if we go over to sequel trace are starting around the sequel server with the select statement in 190.01 at every role from their rules the animals it was other activity other than the trace is that the segment completed to this was the start free of 200.01 a single statement went sequel server now where is this really become significant to us while simply about this is that if we were to copy this in 210.01 numeration of the query and innumerate the think twice that s it I was back on the sequel serves as clear a trace that Singh was that origins 220.01 can build to a defining query enumerating it wants and there s the match going to sequel server is not finished yet because you haven t read the 230.01 whole results are thrown out of his queries with the runs occurs from a survey because they bash starting a statement finished leading United query again 240.01 And that is what happens on the sequence over it is we don t query the sequel server again threatening up to queries here the cars we enumerated that I m 250.01 query twice that in the programme and that may be what you want you may want the latest freshest day to hear all your expectation may have been only defined a 260.01 query here it was actually executed and that s not what happens however if that is what you want to have happen we can take this query and we can force 270.01 its execution right here by calling her unethical to list on and if we got a list we get different behaviour forgot look at the sequel server near a trace was 280.01 put back a single step along he now notified and is now it is really different when we define the query here and in call to list on it all happens 290.01 we will actually go and execute a query at that point and agency is executing a query from thereon in as we numerate around and so on we are 300.01 Executing anything on the sequels are so runners programme to completion and you can see back on sequel we just had that one statement in order to 310.01 get that done so very different behaviour very important behaviour that you want to be aware of your views framework now the other interesting aspect of this 320.01 perhaps isn t quite so obvious let s go ahead and the changes go little bit to take a second enumeration stayed at the two list and 330.01 must just have that race but it will act working code now I said was that when we define this query here what the reader is capturing 340.01 if you like the intent of the query framework as an into query will this use to capture the intent of queries if you like as expressions 350.01 and expressions originated tiny collage interrogate 20 define this review we captured me intent of the query what s interesting about that is that we can extend it 360.01 It that have won Michael a filtered query and what do he was also from sleep in the original query as unaware calls where 370.01 seed art country for instance equals UK something like that and will select the animal videos with a numerate through the filtered query rather 380.01 than the original query and says that as clear as articles of the trace of comeback and a single stethoscope to have respect to 390.01 this code here redefined one query as you might now expect nothing went to the database we define at a query based upon that 400.01 query as you might expect nothing as to the database and only when we come to a numerate will react to generate the right 410.01 query to go to the database and select in this particular case customers are live in a particular country with other countries and from a parameter 420.01 To notice how powerful this is because we didn t execute the query here we can add to it as we go along ridges are a really powerful thing to 430.01 be wanted to do and certainly in the past you ve only thought about having to do various different list filtering on a website for instance almost sourcing back beetle 440.01 and see if you can probably see how you might do that kind of thing here don t you aspect to what was seen wiser queries are not executed ready 450.01 to find them and that means if you enumerate them five times you will execute them five times the other thing is that because they re not executed when you 460.01 define and you can extend them as you go along Unicode summary of what the senior look at how to define a query and we define it is not actually 470.01 executed each of the executed everytime we enumerated and it is fantastic that allows for us to do dynamic additions like this or an associated aware clause to agree that already existed 480.01 And you can use methods like to list is a few others did a similar thing in order to cause the execution right there 490.01 and then a To its results so that whenever you numerate your using a result you already have rather revisiting sequels are now where 500.01 can you go to a more measured online there are enter on yen the URL that their on screen it will provide you information 510.01 on the link project including an equal weblog linkinaction net and encourages you have a look about you like reading out and block and 520.01 finally if you want plant and feedback on the enjoy the fashion e mail me on my e mail address is just their on 530.01 screen food and memory are tragic west London fashion other than that Ltd they could have lived in and out catching a
Deferred Loading of Related Entities in LINQ to SQL
Overview
LINQ to SQL has capabilities for controlling when related Entities are loaded from the database. The framework has default mechanisms but you can override those and take control. In this video we'll explore how this works.
(Duration: 12mins 0secs)
0f5c1df4-aa0c-41ac-a488-27660b3de165 0.01 Oh mines might told him a little Microsoft in the UK and this is one of a number of short sessions about a new technology that is coming with 10.01 visual studio codename orchis that technology is called in to sequel and it provides the language integrated query pattern on top of the new object relational databases API it 20.01 is artificially in August and telephone in version 3 5 now as it said it is one of a series of session so if you ve not seen the 30.01 introductory session powder with that sort before delving into this one But here is something always seems to crop up in object relational mapping frameworks which is how do 40.01 we represent relationships between friends of the customer and their orders and as we navigate our relationship from a net programmers perspective navigating from customer records are a bunch 50.01 of order objects how do we best make use of the database to ensure that monopoly and that too much later and we don t need it 60.01 It little later when we do need it and it is idea of lazy or deferred loading of collections of objects from the database 70.01 that once looked back as quickly here and I m here in visual studio operas which was the latest publicly available version of the 80.01 time that we recorded the video and I ve already gone ahead and use the diagramming tools in previous sessions to bring you the 90.01 customer and the order table from Northland and having taken go ahead and create one of Northwind data contacts are seeking ex one of 100.01 those things service on my machine database it s all also in and a dramatically let s say from ceiling and context of customers 110.00999999999999 that select safe now because in generated code that is going to look at a generated conference open and in here from five 120.01 Feel the orders of the table gold orders because we found it here the visit of a customer we have this 130.01 entity set off order called orders and we spoken out as previously as a way of modelling a primary key foreign 140.01 key relationship in this go to want in any relationship between the customer and the orders that he or she has 150.01 and if it is that class does many slots are for us with respect to loading data when we actually need 160.01 it so I mean by the word go back to programme and let Scotland in UEFA and this query which is 170.01 written so was a fridge customer in the query that console write line and customer orders count is disgruntled that code and in 180.01 Fact that unaware causing here to get slightly less noticeable that the little same received country equals Germany s something like that okay so I ve got 190.01 a sequel server profile of those running in the background is we can see what interactions of its sequel server digitalis held at and run it with 200.01 really have one query going on here where we go and get all customers of the Germany has run this code base account of the orders now 210.01 some people watching this will probably get surprised that we queried the customers and yet we seem to be able to also access their orders would clearly 220.01 come from another table so this is a little bit strange what a reality under intense interactions with the sequel server well if we dialogue about profile 230.01 trace was a quite a few queries of the across the sequel server initially what we ve done is selected the customers of it in Germany 240.01 And at seashell gold loops around that accessing the orders collection from the net perspective always been doing is sneakily going off selecting the orders for the first customer 250.01 selecting the orders of the second customer selecting the orders of the third customer and so on and so forth essentially what we re doing is deferred loading or 260.01 lazy loading or whatever you like to call it off the orders for each customer as and when you actually ask those orders and that s one of the 270.01 pieces of magic that the entity set a class does for us though clearly there is a trade off being made here we are making more round trips to 280.01 the database than you might necessarily need but were doing that in order to reduce the amount of original data that we get back at the point where we 290.01 first innumerate query to the day of its number of roundtrips versus amount of data potentially was redundant that has brought back a conventional control over this though 300.01 Because the deferred loading capabilities in the context of something that you can switch on and off to let go ahead and ask context to 310.01 stop doing deferred loading note that you need to switch the off before you execute queries are differences with the gone transept that down here 320.01 and what will find it after Iran begin at centre and the applications they were not allowed to do that at that point so it 330.01 is that it before we start using it let s will set it up here now are saying that there is no deferred loading and 340.01 if we clear out sequel server profile at that point we can run we can see that now the order s collection is absolutely not 350.01 been brought back from the database for that particular customer so we re no longer going that automatic deferred loading working for us so we can 360.01 Deferred loading for everything now on that particular context what if this customers to order s relationship was something that was the one relationship where we 370.01 did want it so long to ask generally both customers and orders we wanted it on or he can manipulate that this to a classical data 380.01 shape so again before history queries and requirements over what I wanted a new data shape of clash if we create it and what we can 390.01 say here is that when we load customer will also want to load customer orders have redeemed at a go ahead of the scientific context the 400.01 shape through the shape that has created them will find that it goes back to doing that deferred loading behaviour surely the customers and others out 410.01 everybody got this one relationship to us with an academic reality which is switched off deferred loading for all relationships apart from with a 420.01 That by saying well if it s a customer to orders relationship used if that loading on again forgotten and run at that point will see 430.01 that those orders and had been loaded that one and only relationship that this might cause you set this leadership like this there is the wave 440.01 on setting it without getting review context and recreating a context that the something to be aware of now and other thing to be aware of 450.01 at the moment we switched off that loading the context in which city of the customer to orders relationship but we switch over every single customer 460.01 like the cases were really want certain customers is what we can do there is another method on shape its gold associate with Silicon say that 470.01 we only want this way see orders where I were to go ahead and say order custom build up city for instance evil is a handler reform cost 540.01 And education were not have the show could also rediscover the shape is given at and we could simply go ahead and private printing orders 550.01 but counting with the popular deal is collection ourselves so we could say single orders door another sign those orders so that context will get 560.01 the orders will soon wear Tuscany oh order Surrey customer ID not customarily so if I was doing ourselves our population holders collection by simply 570.01 gone back to the databases are getting all the order to this particular customer so you have a flutter that is you want to run 580.01 that was the all other s collections populated so eventually got lots of different choices and get automatic deferred loading done for us with switching 590.01 off the controller today to show for him and he gone that Internet experts ourselves wanted a full spectrum of the options you expectancy a 600.01 A year if we go and object relational framework were always like to get into this gap between primary and foreign key relationships in data that it was once many relationships 610.01 and the natural way that we navigate object hierarchies which is just to access properties wished for want in any relationship would look like a collection to the has to decide 620.01 what to do it in the link to sequel at the time of recording the videos as the default of the framework automatically do deferred loading on the children collections as 630.01 we saw and this can mean that if you ve got a thousand customer records with 10 orders each that can generate an awful lot of roundtrips to see the serving 640.01 of the popular all of those orders collection should you ask for them we only get them if you ask for them but you have the flexibility because you can switch 650.01 this offer a particularly complex as they stop doing that we saw that and while she switched it off you can angle obtained that the behaviour that contacts we saw you 660.01 Dinner article data showed and allows us to say well gay switch off deferred loading generally will switch it back onto this particular relationship and I m sure that 670.01 we can even traditionally switch off particular relationship at the so that even either of those things were frozen political blogging orders ourselves when everyone to got all the 680.01 flexibility think that you would need just to go to decide how you wanting to work in England and Rome offer now where can you go to a more 690.01 measured online there are enter on and a yen URL that there on screen there will provide you information on the link project including an equal overwhelmed up a 700.01 link in action net and encourages you have a look about you like reading out and blocks and if you want my country back on with enjoying the fashion 710.01 e mail me at my e mail address is just own screenplay to mail me on tragic west London fashion other than that Ltd they could have lived in a 720.01 730.01 740.01 750.01 760.01 770.01 Again and
Querying with Stored Procedures in LINQ to SQL
Overview
LINQ to SQL isn't necessarily all about generating T-SQL from your .NET code. You can also query data using stored procedures thereby gaining advantages of security and control. In this session, we'll take a look at how that works.
(Duration: 24mins 47secs)
979116de-9ab9-482c-ab32-4cced9f21c5f 0.01 To lines might ultimately work at Microsoft in the UK and this is one of a series of short videos about a new technology that is coming with visual studio 10.01 codenamed August that will be the next version of visual studio that follows on from visual studio 2005 new technologies called at link to sequel or language integrated query to 20.01 sequel on what it does is it puts a new language integrated query pattern that we see a lot of places in visual studio codenamed August on top of the 30.01 new object relational mapping API that allows us to get effectively objects directly from the database now this is one of a number of sessions if you haven t seen 40.01 any of the retina of out looking an introductory session and pasta sessions about mapping in the day to conduct in this session were settled at using stored procedures for 50.01 querying data from the database and a subsequent session will look at how we can use the procedures for modifying and the database separated us think that training sessions at which 60.01 Author in his video and amusingly much technology preview of visuals you are walkers that was the latest version available at the time of recording 70.01 the video so that start off by popular with students at writing school procedure and procedure will cause procedure empty ban them one and all 80.01 that are doing here is select staff from customers in north Leinster s great that then straightaway to got deep you were guaranteed him a 90.01 one spotlight visual studio and into blank project is very right now such a move that Coke is one I ll get it lanolin singled 100.01 out a money done in other sessions will go out and out into this new diagram is a new item ran down to the bottom 110.00999999999999 of this and his equal will cause no swift little lesson in many many sessions and and under this will use a server explorer to drag 120.01 In the first instance of customers they will thought under a customs table onto the then and I didn t like a silver explorer go to my store procedures Daniel 130.01 is refreshed at this a little while it has created NT demo one and by the drag it onto the diagram to debate the store procedure and ragged in this 140.01 instance I should write off a blank space within the diagram as when I look at the code is generated when we do that for empty one can savour a 150.01 spot over to the generated code and just as a side what we were doing and dragging and dropping at the sequels of the trace running in the background you 160.01 spot the lot went on in sequel server is that we did effectively just go ahead and execute that store procedure with format only set the essentially the framework in 170.01 visual studio strike to determine the shape of the data that comes back from the store procedures and code generation around a task force when I m in the background was redoing 180.01 Dragging and dropping back in our own visual studio suddenly generated code lets look for demo one and you see that what we got was a 190.01 function this function has been added to data context as an additional method on their and you can see it and actually do lots of ways 200.01 is inactivated so it is a procedure at the moment is returning in my innumerable other type of empty demo 10 in the second I goes 210.01 off to Colson based glass cone of the action executes the procedure and get something back for NT demo one will find that out of the 220.01 classes and generated in this file and it looks an awful lot like the customers table so we got all the columns and is one of 230.01 expectancy from the customers it will every pub and tried out with a cyclist was so racked the diagram and will get rid of empty demo 240.01 Understood procedure saw it then it is back to the server Explorer and has done drag store procedure run a drag on to the customer table and if we look 250.01 at the properties of this fingernail will see that it actually has a return type of customer as opposed to Antigua and the one which is how it was defined 260.01 before and if we don t look at it difficult to generate code now that will look for a store procedure you can see that it is now returning a 270.01 set of query results of type customer so we change the returns out to be the the type that we had on our diagram is probably what would want in 280.01 this case as opposed to a newly created type and actually it was no type Corresponded to the results that are the still procedure because it was being joined and 290.01 so on you have to go with the newly created results I ve has a policy reducing one of the entities for a drop now with that in place with a backdrop 300.01 And we can create a Northland data contracts only done so many times before Scott context Villa connection strained and 310.01 then we can cause still proceeded to be executed as right aquarium was so from a hundred seen in context 320.01 object sorry for the NT demo one an overspill procedure in its desolate sea and it is ever each customer 330.01 see in that query less is right out as their ideal something other and it s the show that we 340.01 are not losing any son of flexibility you let go and select and finally numerate rather less well and set 350.01 the region severely am somewhere and want to regret doing the Mandelson database unless a long course of nature 360.01 And is everyone built that code we should see that we get all that data from the database and figure back to 370.01 Northland will probably find that when we select region from customers that he could select distinct region from customers what s good 380.01 books customers that we lead in an updated set of since to somewhere so in this particular instance we went ahead and 390.01 executed that still procedure in order to get our customers and we still could do the stuff that we are expected to 400.01 live updates and so on this by simply changing properties on the customer entity that we ve got now won if we 410.01 had wanted to call the still procedure with a Parramatta bustle back about useful procedure probably appears greater the procedure looks and will take the 420.01 Sofa are under unsafe country by lavish the country column is in Northland s controller It is 430.01 then go off and threw it out of them and the first place s procedure demo to 440.01 sodomising a wrong smidgen of the country s myth of a from a procedure and let s 450.01 try and call that from coat to go back to a diagram wrap ourselves unusual procedure from 460.01 us wrecks were such as reverse a list and egalitarian and that written a customer again lets 470.01 go ahead and closed at off and say yes and will call them to now 480.01 Customers who are in the UK and that s not update their region any more assist on lifeless desert castle was built and it 490.01 is it will bring back those customers that lived in the UK and recalling across that a procedure that in e Stiller and now 500.01 where does the procedure had outgoing parameters posted just incoming parameters so that s what I was a single server that is the biggest 510.01 of procedure demo to animated demos three and will simply have a second row materials collects and I know that have joined it can 520.01 be of type integer and will have output on that thing at the end of this group will do this was set outgoing 22 530.01 Algonquin procedure demonstrate and let s trying and dragged her across to a diagram in visual studio two o clock like it here with 540.01 Diagram within a server explorer will refresh that Mr still proceed in the three Bears dragons recrossed to customer entities here I noticed that 550.01 we can see we ve arrested in 32 and going here and so if we go back to code that is closest over office 560.01 of changes then went on to called them a three minute at a country now and will seek to pass a reference to an 570.01 indigenous or pass reference to output parameter which will need to define or defy nothing here legislative zero and absolutely rated the query US 580.01 go ahead and right at the Consul value of output parameter and run that only can see the beginning 22 back of the store 590.01 procedure fire that output parameter so things are referenced and manipulated in a natural way from the net point of view 600.01 Now imagine because the procedure doesn t have output front has been set as return values does not back to articles of window hand 610.01 but paced in essentially a copy of demo three scrum ground on and other than the fourth vote instead of an unpalatable order as 620.01 we does have a written statement it was 100 on a sort of it procedure that empty demo for we can go back to 630.01 our diagram in coatless congrats that and the fact that new procedures just refresh again demo for onto the diagram a written customer now 640.01 at the moment I m not entirely clear it s a recurrence of the video how we make and the return value comeback via 650.01 the diagram until the problem is always doing it but a shade at the capability to build a framework hand them will have a look at 660.01 And calling it is a little and close diagram not to be generated code over here and you can see that at the moment NT demo for 670.01 his function the returns on a list of some sort of customer and it takes a single parameter country so and that it is a little wraparound 680.01 top of that will have another function here will call this public and 70s would return the same type as this thing here is a case that 690.01 it got caught of article empty coral demo for an underpass country and would also pass parameter for the return value hold it is with a century 700.01 has go and change the function has been generated for here so that it returns back to as this I query was is that it contains return 710.01 value then we can see that wouldn t have a sense if we look at that result was generated here you can see a return value 720.01 Thought if I don t have to liveability generated code and this might be necessary because of my lack of knowledge it might be that 730.01 the tool is still work in progress and is adding to its this but it is one way of showing of the capabilities are in 740.01 the framework thought it ll change this thing so that it returns the enquiry results that we ve got here is of course right that 750.01 James returned that this function is that it just returns are directly and have that such an everyday review result that yet is littered with 760.01 little and return a hear from flat function and I will do is will have this new function and he called them for all the 770.01 existing function NT demo for and grab the return valuable it back in the Sampras so what the alive end up doing is getting a result 780.01 It is going to have this type of just what nothing and get its title for a result of adding and undergoing empty demo 790.01 for with the country and then they return value equals in secure golf resolved to return value and only to return that back and 800.01 recaptured the return value at the same time so forgot that work only for this little matter on top of what was generated for 810.01 us again as this is not the lack of knowledge of my party might be something tools yet to add up to a code 820.01 of the here and we will call demo for all with the UK and Aceh return value by output will change as vanity unit 830.01 of the return value passing it by output and at the end of the function imagist writer to the console buzzword in 840.01 Compile and run s compile it out with a where we shouldn t have one astronomer Ali can see that 850.01 we got our results that only also captured a return value is definitely there in the frame of the something 860.01 framework and return to as I am relatively easily now what everything is property to the returns more than one 870.01 adult type has got back to here ready new procedure is fully demo five that is the case that in 880.01 here and simply would select staff and customers anglicised from orders and that procedure five if we got that by 890.01 diagramming tool then open up that diagram let s dragged onto this is refreshing list once again Andy Demo five 900.01 Now it will really make sense to drag it onto the customer entity he necessarily is very onto the open space and 910.01 let s go and have a look at the properties of that policy that is going about with an auto generated returns 920.01 at the moment is voted out under and if we try a procedure run Alice Goncourt empty demo five doesn t have 930.01 any parameters and would have gone right out whatever we get back from that query that has got sharply in the query 940.01 was right at a variable to the console and build a runner and we see if there s forgetting one of his 950.01 empty demo five instances back while those look like with the promises of the debugger will see that an empty demo five incidents looks 960.01 Awfully like a customer silicone bacteria is right now is the customer is not a customer and then a result of orders is just the first results 970.01 are we going back so if really look at the generated code friend he can revive up over their easy to empty demo fire is currently returning 980.01 a list of these things which look like customer records now we can change the definition of this in order to make it bring back multiple results 990.01 at let s go and take a look at our diagram for a second because one of things you don t have on the diagram is now 1000.01 is the orders table from Haltwhistle as anti orders to Atlanta this summer you understand one order is and we can they will revisit the generated code 1010.01 here actually at any time of tapping into generated code you would have to visit it elsewhere because a code is regenerated with Iraqi packing away the generated code 1020.01 But we can but generated code here and we can really define how the store procedure is to be interpreted to centred upon where 1030.01 it is called Xanadu s attributes on this and that it is in the designer hasn t worked this out automatically just yet in 1040.01 this particular preview at the very least out of it vehicle result type which has gone results are am this will give it a 1050.01 type of customer and will also add a result I ve fill a type of order so we re going to change the return 1060.01 value of the return type of this function to a title fight multiple results to undo that like to change the way in which 1070.01 we are making the call in order to achieve that but is refusing on to online here because this generated a 1080.01 Interesting so what I do is to change the way that the function works in order that the advantages return in several investor execute 1090.01 that the call were going to call another method in Bay fast framework with his method call with multiple results and then simply four 1100.01 of us tacitly premises and ones which are very similar to the existing parameters freaking at the right number of races there can perhaps 1110.01 compile a Summit Mr something fear a situation we can get that right and second row to their company just looks fine to me 1120.01 initiative with the builder a case of an outgoing execute method called multiple results we ve hacked and generated code in this case naturally 1130.01 we can put this in another partial class file instead which make more sense really well be told at return and I multiple results 1140.01 Told it that we have a customer result time and then all the results I let go an unchanged code in order 1150.01 to make this work so I will do here it is it is real go ahead hand define ourselves a result from 1160.01 calling that procedure on and will look around the customers see in resultant art we can all get result of the Tyne 1170.01 Theatre giving a customer s advance authors rightly and then will leave around each order in resolve get result of order and 1180.01 again the writer to consult older order ID and houses will consult lottery line in the middle of those things this week 1190.01 as he was going on as builder and runner at our customers there is orders a legacy that really matters to results at 1200.01 Now is important to realise here that I m using my knowledge of the store procedure to know that the first thing he returns here is a list of 1210.01 customers in the second finger returns is a list of orders if I forget these things the wrong way round so for instance if I was to try and 1220.01 take the first result of them put back as a list of orders and what will find that case is that it will get a failure because we now 1230.01 trying to turn essentially a list of customers and a list of orders so we have to have some knowledge of what were doing out of it that little 1240.01 bit further that still for instance another store procedure where we don t actually know what kind of result we get back until run time so let s take 1250.01 this and build them as sex and let fans into this procedure are somehow result time which will be an integer I will simply say if a result defeat was 1260.01 Hero and the select staff and customers lies in go ahead as lesser orders let alone create them of six at back 1270.01 to code and other leading the design which will break and the hacks changes raids which generated code at his copy the 1280.01 code for them a five year phased in over on top of them as sex change the signature of this letter to 1290.01 reflect the fact that we ve got a parameter coming in here parameter in the name of that parameter is going to 1300.01 be a result I ve and it is an integer or call result idea as well animal may shortly pass that when 1310.01 it comes to calling a method so the contrast result I have there is little we can compile a register 1320.01 But also visually changing rooms at five to six laws were a thing trouble at a spotlight programme finally executes to a procedure 1330.01 tennis so there are 659 to Zero if we get a zero then were expecting to bring that customers so we should go 1340.01 to innumerate customers in that case as front runner there were enumerating customers if we pass one then we should be enumerating order 1350.01 and within a change that to be oral and right ugly order ID is run to their there we are getting orders so 1360.01 we can dynamically select result times as long as you ve got a store procedures attribute with the right types and that we 1370.01 know at the point where we asked the result what kind of result is we can t just be loosened dynamically worked it out with 1380.01 Really know at the time of indication so I know that passing a while here results in order and zero results customer just summarise quickly what the senior 1390.01 which looked in a store procedures for querying we saw that we could matter still proceeded to return a enumeration of an existing entity type like the customer 1400.01 or the order that we ve been playing with we also saw that we could matter still proceeded to return the new entity type generated from the metadata 1410.01 of the still procedure and that will probably be most relevant way you were doing some kind of joining the still procedure and bringing back something that didn 1420.01 t really represent one of your tables we saw that you could use parameters would have those as input or output parameters we also sought to To return 1430.01 valleys from store procedures and looked at what we might do was store procedures written multiple results at all with variable results that swear dependent upon parameters we get back 1440.01 Shape of data from the store procedure in a files that you will take a look at how we can use the procedures 1450.01 for p insert update and elite operations that you might want to take control of when working with winter sequel now where can 1460.01 you go to get more emotional main there are enter on yen the URL that their on screen it will provide you information 1470.01 on the project including an equal allowed up a link in action net and encourages you have a look about you like reading 1480.01 out at blocks they can t provide feedback on a specific enjoyed the fashion e mail me on my e mail address is 1490.01 just their on screen to mail me on triumphant questions on fashion other than that Ltd they could have lived in and out
Updating with Stored Procedures in LINQ to SQL
Overview
Take control of your inserts, updates and deletes by passing changes through your stored procedure layer on their way to the database.
(Duration: 17mins 37secs)
1b4b61c5-e1cb-4f7b-b9a7-bc8cf5125331 0.01 It is only in visual studio August I have the much technology preview which was the last review available at the time of recording a video of a likely 10.01 done in various other sessions with a simple piece of code based on a link to single diagram at the moment I don t understand the customer table from 20.01 the northern database in a my code liaison was non existent in the Swindon which is good a method here ever seen in other sessions that were going to 30.01 insert later when he can delete later when you come to update data them into single frame of display in that it will dynamically generates equal for as corresponding 40.01 to the instance of Labour disease and performed audio updates that we ve made the data in changing it and the framework tax changes as we make them and 50.01 can generate his sequel from the changes in its track but that s not the only way that we can work with regard to modifications in the 60.01 Sequel we can also work by having stored procedures take care on Monday Tuesday so in the first of the Thursday 70.01 control apparently deletes some other with sickle cell heroes kindly publico to be had from previous sessions are at greatest procedure 80.01 will call this the procedure are guaranteed to the customer if I take her customer ID which in north when the 90.01 Israeli end shall find I believe and all that it is the simply set on account and will delete customers where 100.01 customer ID because customising so that it are still procedure is not particularly elaborate metaphors if we can create nothing but 110.00999999999999 Arsenal procedure has just checked in an something of customers were suffering his Mitchell that time because Roddy is indeed 120.01 And Channel 5 is that no one looks at his own charter is probably just drop us to a procedure and recreated apologies 130.01 within family creative and sharp end is together correct and were back in business so if it is still proceeded to delete some 140.01 customers for us we can got back to visual studio and we confined ourselves the diagram that we withdrawn and it is not 150.01 at my server explorer and refresh muscle procedures and bring in Antigua the customer just after the diagram here and I want to 160.01 do is save the go ahead and fitness configure behaviour button on the diagram until and I will try and change the way 170.01 that deletes are performed rather than using the runs antigenically caecal thus than customise and that I do called to the 180.01 And and the framework knows that we ve got an argument here customer ID and what lunchtime do here is just match that up to the current value of 190.01 the customer idea on the object is passed that through to store procedure remembers if you look at and then closed and diagrams to save changes recycling business outlook 200.01 of the code has been generated from their lost leader data context will get a couple of methods added here is just an elaborate point away the first method 210.01 we got his body in a significant one this is totally customer know the meaning of this method is significant this essentially says that any leaks if you have 220.01 an effort on the cross on the data context names this way with the lead followed by the entity name like this to the customer then this will be 230.01 used whenever the frame at once to deliver these instead of just generated his or now the best man of the code has been generated using quite right 240.01 Out at sea minute two versions of the customer record here and I don t think it works if we go this route so what were a lot of it 250.01 is just removed that one and then I think the framework will find this method and instead of performing delete it will call this method may write what this method 260.01 s easy going causes other method and teeth of a customer with a customer ID and all that s going to be rescued store procedure like the scene in previous 270.01 sessions so that is the way in which we are hawking and in the process and we could do anything you like when you look this adjusted with the tour 280.01 was causing to run the store procedure for to the point that we didn t only so so let s pop back to our programme over here and write and 290.01 go against there so it could only evade the context one of those and then making a whereabouts can remove a record so will go to the database get a customer 300.01 Lad will use the alt key customers are not only context that customers are single person lived in a record from the database we can then go 310.01 ahead and do context of customers that remove with Alfie customer and context submit changes and if it had I McIntyre so than in place when the 320.01 framework sees to submit changes the needed information it would look at the data context and you will see that there is a method on who the 330.01 claims know how to do the customers that are consequently few others we should seize him celebrate my instead of the link to single framework going ahead 340.01 in trying to do deletes by generating key sequel was gone to happen here as long as the customer ID into the store procedure implication and Arsenal 350.01 decision occasions and were trying to beat that customer from the database everyone are on call at Stoke Rock will actually a failure because an 360.01 For the healthy customer and only wanted to be repeated like that but you get the idea that it taking 370.01 control of the deletion process so just to be complete that go back and try and control the process of 380.01 updates and inserts through store procedures so all this critical hostel procedures and procedure live insert customer and in order 390.01 to make my life very easy we will pretend that in north when all you need to do is insert 400.01 customer ID and the company name altogether soon after the couple of information at the table to find out how 410.01 company name is defined and company name is just in March a teaser were gone for that until after a procedure 420.01 And simply all do the insert for are so low that those s if you only touches a 430.01 Northland and reality managed to be wrong where missing out on braces double another and about a diagram 440.01 once again that we can go and find our code as it stands today we can bring in 450.01 aristocracy from the server explorer s fresh to his own circus is one change the behaviour of this 460.01 inserts were not customising that he was insert customer and customer ID is going to come from across 470.01 the customer ID and company names that come from the company name is okay that 480.01 And now forgot that the design coat was either we ve now got a call to the use of customs to a procedure with a method is 490.01 love use whenever we come to insert a customer and so again if of the rate went on to that and we go back to program code 500.01 lets disco insert a new customer and an Oscar and so will have customers see if s new is rightly demo five will have a company name 510.01 them at company and will go out and simply add that to the customers of its art and other primary cosmic changes to frame a spot that 520.01 new customer record it should also spot the fact that we have now had generated method and every better fighter run this leading series landed in into 530.01 a customer method here is the new custom agencies and an accompanying demo five if we allow this code to continue it will cull the insert customer stored procedure 540.01 And that will insert the customer into the database but notice that in in into a customer method here you have the freedom to 550.01 do whatever you like your four customer record just valued in the customer record in out of four customer apathy can do anything you 560.01 want in this particular case we are using it to cause the procedure was to particular parameters but you could do anything you like 570.01 just the completeness that sounded something about updates so again to make life easy for myself we will pretend that the only thing that 580.01 we ever want to update in the northern database is the region progress and a fact that statement the company name because another definition 590.01 of that particular column so so the only thing we ever wanted to change as the company now not very realistic to a son At the table 600.01 And 40 is a let s go and create us the procedure so that it will on the 610.01 lives are going to create a story computer rather than a stored procedure is rigorous to a procedure 620.01 that updates you easily at malice or visit the diagram present and let s have a solid silver 630.01 explorer refreshed that to get the update customer within an pop back to this thing configured behaviours that 640.01 updates customers that use the update customer when he gone to update I will probably want to pass 650.01 the current customer ID as the primary key but I d probably want a fast 660.01 Original sorry the current company name that will be the modified version in fact more at Warrior Rick realistically I can do with the original customising as the primary key 670.01 doesn t change and the company argued that with the current modified values I took it that let s closes down and have a look at the code has been 680.01 generated from a was seen in a method update customer note is once again and this is very important you get the whole customer record as it stands you get 690.01 the original customer record as it originally came from the database into your programme so once again you are not limited to just corner store procedure with certain parameters you 700.01 can do anything you like but in this particular case were withering scorn update customer which because rightly originally shouldn t change anyway and the company name as it currently 710.01 stands so once again if it were a break point on that method we go back to code lets pull a customer record from the database or 720.01 Customers see yet that same personally I before the elderly customer change here is a company 730.01 name to be my company that will submit changes at present five was if we consider 740.01 our position is on in the global to see you should be foreign affairs five and 750.01 only it grapevine we can see that currently the customer looks to be in a company 760.01 name originally sorry rescue nurses are worth of property and the desire and I felt a 770.01 slight bug in the cogeneration here in saying that originally the customer is in my company and currently 780.01 Customer seems to me in this company typhus like medico generation and rename his record littlest original let s call this current nursery 790.01 run a piece of code and every highlight these two things you see that originally asked key works a s there and now 800.01 in the current version he is working her company called my company which is what we just changed it to and anything got 810.01 a customer will pass asking will pass the current company languages my company will call the still procedural video that in the database 820.01 now one of the things that really comes into play when you take control Vincent s updates and these drivers is that the 830.01 framework is an amount of work around its optimistic concurrency model that looked at in a previous session on concurrency angiography or the benefit 840.01 That he is going to have to do your own concurrency checking from the point of view of updating records and make sure they haven t changed my something out before you 850.01 update them and probably making sure they haven t been updated by some that if we delete them so the work that the framework does for you there is it something that 860.01 you will have to do yourself because you taken full control over what happens with your insert your updates and you lived in Leeds also if you need to work out with 870.01 respect to transactions you can always go ahead and described the transaction is in flight from the transaction property that is hanging off the data context you are part of the dead 880.01 content classes we can do that kind of work so you are thinking of control over how things work and you have to potentially do a little bit more into the effort 890.01 you ve got put in because you doing that but it gives you that capability of using store procedures for inserting updating the meeting antibodies or previous session he can use of procedures 900.01 To advertise together nicely if you are the son of shop where you used your procedures very much relaxes into the database whether that be for reasons 910.01 of control of security or perhaps even for recently similar pattern of performance and so on so that that s really what you get by taking control 920.01 is where the ability to call Rocksteady work for you as we ve seen with Internet updated daily s to what was seen here we seen that 930.01 the process that the framework usually takes for generating key sequel for your insert your updates in Italy this has been tracking for you process can be 940.01 hooked and the way it works is that we method to data context for updates into and really given special names update customer is a customer and 950.01 so on and instead of generating key sequel in the framework sees those methods have the right signature it will cause methods instead and will 960.01 And the methods with the necessary data to do whatever it is they need to do so in a sense of and updated the present before record and the 970.01 after record for an insert of the subsidies and the new record and pretending to present the current record the designer we saw supported their solitary drag and drop 980.01 procedures of the design servers we depend on customised behaviour for particular entity types and map the behaviour crossed still procedures and in the graphical designer we could go 990.01 ahead and say that parameter on procedure came from a particular version of a property on the entity objects were playing with and actually we don t let different 1000.01 ways of doing mapping in one of the early sessions and a different ways will also apply to work in the store procedures so is not necessarily the case 1010.01 you have to graphically design although she could also do it through and code generation tools from the command line or a from XML as opposed at 1020.01 Sit and play with the design in order to get this onto a screen now you got your moderation 1030.01 on May there are enter on and yen the URL that there on screen they will provide you information 1040.01 on the link project including an equal weblogs are a link in action net and encourages you have a 1050.01 look about you like moving out at blocks and finally if you want applied and feedback on the enjoying 1060.01 of fashion e mail me on my e mail address is just down the screens we can mail me 1070.01 out I had questioned on the fashion other than that Ltd they can about 11 and a
Using SQL Functions in LINQ to SQL
Overview
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.
(Duration: 9mins 28secs)
448cb192-7850-4b65-a8ee-189d1333af6f 0.01 Oh minus one told you are 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 10.01 that is coming with visual studio codename porkers that will be the next version of visual studio the technology is holding to sequel a which integrated query to sequel what I 20.01 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 30.01 of videos if you haven t seen introductory session to session on the data context of the sessional mapping it may be worth another look at those before you pitch into 40.01 the middle of the session he has undertaken very quick look at how we can core functions within queries and Italy and thinking about the substandard functions exist inside the sequel 50.01 so how 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 60.01 Gateside in visual studio August I have the much technology preview that was the latest version available at the time of making the video and is 70.01 likely to so many of these sessions I can t create a very basic console application into Consul application I ve added a link to single 80.01 diagram from a northern database with customer table and the order table to more correctly the customers table in the order is terrible represented here on 90.01 the diagram which is regenerated code lets me programme against a data context and get hold of these things from the Northampton database software acknowledges them 100.01 almost non existent readers have a data context allows it to get hold of Northwind and entities and then you can start a programme against them 110.00999999999999 one of the most magical things that the reach sequel framework from me is that I consider a pretty simple query but I do queries such as we 120.01 Right to the console the number of customers in the customers they also will go ahead and get context customers dropped an earlier extension method called Count software then 130.01 can do that core count on it will build a piece of code I have got a sequel server trace running in the background so we can run this 140.01 these of code we see that there are 92 records on a table but if you look at the sequel server trace the Israelis I think I surprising in 150.01 the first instance the sequel was sent to sequel server in order to make that work notice what he will select Staff and customers we didn t own query 160.01 the customers table and then go ahead and string that back to the client account records back on crime the right thing in terms of the tea sequel that 170.01 God generated here and we can go further without that about a code and what will do little right to query so so query is from seeing in context of customers 180.01 Russell ex seed country length or something like that and over the sofa reach into the video that another striker to the console is long run 190.01 that there is the length of the countries that were really nice and the sequel server and once again I forgot the face from this will 200.01 see it all as you d is calling the length function inside of sequel server women are even bringing those columns back to the client in 210.01 order to do that work does have something else what it wants to bring back a page of data from somewhere in the customer s table 220.01 such aimed at query here and and so from seeing customers lets you select seed customer ID or what if we do not hold that it 230.01 would have wanted some section of the table in the middle where it is will say through each strange that so as to riding again queried 240.01 Analyst is skipped a certain number of records let s skip the first 30 records and limits followed by taking the next 20 records 250.01 the third arrived at the console customer ID was built that was the runner there are some customers from Northwind but when I look 260.01 included outside the sequel server that is holidaying is using in this case specifically single 70 005 capabilities such as road numbers and over 270.01 in order to order things and then bringing back the top 20 results from the two generating a really smart query to sequel server 280.01 2005 we would have to use a different office in Conservative hours in the framework supports that found the seizure output that we wrote 290.01 the skip and take to we have these functions are being translated into really clever pieces to sequel for a hand was naturally by the framework 300.01 Looking again at more examples of things we can do I so wanted to go to the customers table where 310.01 for example heroes should get rid of that but so where the Sea Dart country but CC dog contact name 320.01 contains the straying down their lesser something relatively find another scholar amateur select the customer only their and we won 330.01 t skip here any more because we might not find any records as front runner that must build that up 340.01 to selection query their CD contains method on system s friend here but are rather against database with a couple 350.01 of records that there had eluded over the sequel server with the right clause s translating methods and system strain in 360.01 To alight closer sequel server and has those organisers also will translation system of string that this will be in the documentation for the August 370.01 preview at the idea of heavy sings of being translated everything about mathematical functions may be much and that something like context orders lacks and 380.01 will take the order of the glass itself rate of annual something like that but it was an action that column that run that we 390.01 get value out of it the go ahead and have a look at our sequel trace we can hopefully see that we selected the maximum 400.01 that table at that point there is support from that image in net daytime and timestamp function is not necessarily all of them were quite 410.01 a number into what happens inside sequel server so if you want to try to query such as from oh in context orders 420.01 Where that s what impact he ordered eight the day of the week and let s say where that was on a Tuesday in a selected 430.01 folder or variety from that and_add and innumerate those and put them out so safe reach when query consul arrive unavailable free run at query we 440.01 can see that we split out the little roadies were on the Tuesday and if you look at his sequel has been generated from that was 450.01 either awkward if he is using date heart and convert in order to act city that grew inside the database whilst we like to think about 460.01 it in terms of net types overhearing sign of code just have exonerated a short video regular punters that it was to show that we can 470.01 hold functions in England to sink or query and they turn into functions inside off sequel server to its political string functions like 480.01 Contains the length 800 functions and his equal result with the golfing holiday time and maintenance functions and his equal aggregate functions like some character Max turned into 490.01 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 functions into sequel 500.01 so I got on his functions inside of me to sequel queries especially something light max or count you don t need to worry too much beyond the 510.01 initial understanding that the framework is doing the right thing in sending the right function calls across to sequel is over for you is not pulling that all 520.01 the records to the client and then working out what the maximum was a bigger wrong way of going about it that s not what the framework does 530.01 listen pretty cool stuff to turn your functions in your queries into the right functions in the right queries inside of sequel so now you got not yet more emotionally there 540.01 At Endeavour on and a yen URL that there on screen they will provide 550.01 you information on the link project including the equal have locked up a link 560.01 in action net and encourage it to look about you like reading our fervent 570.01 locks and if you aren t providing feedback on specifically join the fashion e 580.01 mail me on my e mail messages there on the screen to mail me 590.01 out I had requested on the fashion other than that who famously taken about 11
Saturday, March 1, 2008
BlogPost:ScreenVideoPlayer1.3 is ready!
Hey everyone
Another version (not strictly new version, it is just a small update) of ScreenVideoPlayer is now ready. This has an important change - the labels corresponding to the current position in the video is also highlighted.
As always, the URL is here.
If you have any questions/comments/general feedback please reply as comment to this blog post.
Subscribe to:
Posts (Atom)
