{"id":738,"date":"2007-12-09T03:39:00","date_gmt":"2007-12-09T03:39:00","guid":{"rendered":"\/blogs\/bobb\/post\/Entity-Framework-Beta3-In-search-of-round-trip-optimization.aspx"},"modified":"2016-08-05T07:58:07","modified_gmt":"2016-08-05T14:58:07","slug":"entity-framework-beta3-in-search-of-round-trip-optimization","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/entity-framework-beta3-in-search-of-round-trip-optimization\/","title":{"rendered":"Entity Framework Beta3 – In search of round-trip optimization"},"content":{"rendered":"

\nThe ADO.NET Entity Framework Beta 3 was released this week. For details, see the ADO.NET team blog.\n<\/p>\n

\nOne of the things that bothered me originally about EDM was that Entity SQL in V1 doesn't contain INSERT, UPDATE, or DELETE statements. Only SELECT. So to update or delete a row, I'd have to fetch it first. Let's start with something simple, like an INSERT with a foreign key constaint?\n<\/p>\n

\nAll of the "add" examples I've seen always insert a new customer and new order and new order details at the same time. But what if your customer already exists?\n<\/p>\n

\nAs an example, the titles table in the pubs database requires a pub_id of an existing publisher. But EDM represents this constraint as a publishers class field in the titles class and an additional  publishersReference instance as a member also. My first attempt to create a new title with an existing publisher (pub_id 1389) went like this.\n<\/p>\n

\ntitles t = new titles();
\nt.title_id = "BU9994";
\n\/\/ rest of columns elided for clarity
\n\/\/ statement to set the pub_id to 1389
\nmodel.AddTotitles(t);
\nmodel.SaveChanges();\n<\/p>\n

\nMy first "statement to set the pub_id to 1389" was:\n<\/p>\n

\npublishers pub = new publishers();
\npub.pub_id = "1389";
\nt.publishers = pub;\n<\/p>\n

\nThis one tries to add the publisher row too and gets a duplicate key violation.\n<\/p>\n

\nThis worked
\nt.publishers = model.publishers.First(pub => pub.pub_id == "1389");\n<\/p>\n

\nBUT it causes a SELECT to get the publisher from the database. I wondered at this point if I really needed to do a SELECT to INSERT a row with an existing foreign key row. With some help from Danny Simmons<\/a>, a much nicer way is to use the publishersReference class and synthesize an EntityKey.\n<\/p>\n

\nt.publishersReference = new System.Data.Objects.DataClasses.EntityReference<publishers>();
\nt.publishersReference.EntityKey = new EntityKey("PubsTestEntities.publishers", "pub_id", "1389");\n<\/p>\n

\nThis does the insert correctly with no gratuitous SELECT. Confirmed by SQL Profiler. Good.<\/p>\n","protected":false},"excerpt":{"rendered":"

The ADO.NET Entity Framework Beta 3 was released this week. For details, see the ADO.NET team blog. One of the things that bothered me originally about EDM was that Entity SQL in V1 doesn't contain INSERT, UPDATE, or DELETE statements. Only SELECT. So to update or delete a row, I'd have to fetch it first. […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"class_list":["post-738","post","type-post","status-publish","format-standard","hentry","category-data-access"],"yoast_head":"\nEntity Framework Beta3 - In search of round-trip optimization - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/entity-framework-beta3-in-search-of-round-trip-optimization\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Entity Framework Beta3 - In search of round-trip optimization - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"The ADO.NET Entity Framework Beta 3 was released this week. For details, see the ADO.NET team blog. One of the things that bothered me originally about EDM was that Entity SQL in V1 doesn't contain INSERT, UPDATE, or DELETE statements. Only SELECT. So to update or delete a row, I'd have to fetch it first. […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/entity-framework-beta3-in-search-of-round-trip-optimization\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2007-12-09T03:39:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-08-05T14:58:07+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/entity-framework-beta3-in-search-of-round-trip-optimization\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/entity-framework-beta3-in-search-of-round-trip-optimization\/\",\"name\":\"Entity Framework Beta3 - In search of round-trip optimization - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2007-12-09T03:39:00+00:00\",\"dateModified\":\"2016-08-05T14:58:07+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/entity-framework-beta3-in-search-of-round-trip-optimization\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/entity-framework-beta3-in-search-of-round-trip-optimization\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/entity-framework-beta3-in-search-of-round-trip-optimization\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Data Access\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/data-access\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Entity Framework Beta3 – In search of round-trip optimization\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Entity Framework Beta3 - In search of round-trip optimization - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/entity-framework-beta3-in-search-of-round-trip-optimization\/","og_locale":"en_US","og_type":"article","og_title":"Entity Framework Beta3 - In search of round-trip optimization - Bob Beauchemin","og_description":"The ADO.NET Entity Framework Beta 3 was released this week. For details, see the ADO.NET team blog. One of the things that bothered me originally about EDM was that Entity SQL in V1 doesn't contain INSERT, UPDATE, or DELETE statements. Only SELECT. So to update or delete a row, I'd have to fetch it first. […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/entity-framework-beta3-in-search-of-round-trip-optimization\/","og_site_name":"Bob Beauchemin","article_published_time":"2007-12-09T03:39:00+00:00","article_modified_time":"2016-08-05T14:58:07+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/entity-framework-beta3-in-search-of-round-trip-optimization\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/entity-framework-beta3-in-search-of-round-trip-optimization\/","name":"Entity Framework Beta3 - In search of round-trip optimization - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2007-12-09T03:39:00+00:00","dateModified":"2016-08-05T14:58:07+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/entity-framework-beta3-in-search-of-round-trip-optimization\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/entity-framework-beta3-in-search-of-round-trip-optimization\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/entity-framework-beta3-in-search-of-round-trip-optimization\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Data Access","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/data-access\/"},{"@type":"ListItem","position":3,"name":"Entity Framework Beta3 – In search of round-trip optimization"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/738","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=738"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/738\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=738"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=738"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=738"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}