LINQ to SQL is an API that allows querying relational databases. The samples in this post are based on the concepts of:<\/p>\n
Considering the known Winner class from the previous posts, <\/p>\n
\r\npublic class Winner\r\n{\r\n public string Name { get; set; }\r\n public string Country { get; set; }\r\n public int Year { get; set; }\r\n}\r\n<\/pre>\nDecorating it with Table and column like this<\/p>\n
\r\n[Table(Name = \"Winners\")]\r\npublic class Winner\r\n{\r\n [Column]\r\n public string Name { get; set; }\r\n\r\n [Column]\r\n public string Country { get; set; }\r\n\r\n [Column(IsPrimaryKey = true)]\r\n public int Year { get; set; }\r\n}\r\n<\/pre>\nwill create a direct mapping between Winner<\/tt> and the table call Winners, and between the fields Name, Country and Year of the class and the columns with the same name from the table. Both Table<\/tt> and Column<\/tt> have several properties. One of them is Name<\/tt>, which specifies the actual name of the table or column corresponding to the class or property. If the property Name<\/tt> is not specified, the same name as for the class and properties is used.<\/p>\n
Assuming we have a SQL Server database, located at C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\UCL.mdf, with a table Winners that has three columns Year (which is also the primary key), Name and Country, and that this table is populated with the winners of UEFA Champions League, we could write the following code to retrieve and show the winners:<\/p>\n
\r\npublic void PrintWinners()\r\n{\r\n \/\/ creates a data context that takes the path of the database\r\n DataContext dc = new DataContext(@\"C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\UCL.mdf\");\r\n\r\n \/\/ retrieves a Table of Winner\r\n Table<Winner> winners = dc.GetTable<Winner>();\r\n\r\n \/\/ creates a sequence of winners ordered descending by the winning year\r\n var result = from w in winners\r\n orderby w.Year descending\r\n select w;\r\n\r\n \/\/ prints the sequence of winners\r\n foreach (var w in result)\r\n {\r\n Console.WriteLine(\"{0} {1}, {2}\",\r\n w.Year, w.Name, w.Country);\r\n }\r\n}\r\n<\/pre>\nFirst, we must instantiate a DataContext<\/tt>, passing as argument the path to the database. DataContext<\/tt> has a method GetTable()<\/tt>, that returns a Table<T><\/tt>. To get the winners we call it as shown above. On this table, we can perform a query and show the results:<\/p>\n\r\n2006 Barcelona, Spain\r\n2005 Liverpool, England\r\n2004 FC Porto, Portugal\r\n2003 AC Milan, Italy\r\n2002 Real Madrid, Spain\r\n2001 Bayern Munchen, Germany\r\n2000 Real Madrid, Spain\r\n1999 Manchester Utd., England\r\n1998 Real Madrid, Spain\r\n1997 Borussia Dortmund, Germany\r\n1996 Juventus, Italy\r\n1995 AFC Ajax, Netherlands\r\n1994 AC Milan, Italy\r\n1993 Olympique de Marseille, France\r\n<\/pre>\nIt is however recommended that we use a so called strongly-typed version of DataContext<\/tt>. In other words, a derived class from DataContext<\/tt> that keeps as members all the table collections. In this case we don’t have to call directly GetTable<>()<\/tt>.<\/p>\n\r\npublic class UCLDataContext : DataContext\r\n{\r\n public Table<Winner> Winners;\r\n\r\n public UCLDataContext(string connection)\r\n :\r\n base(connection)\r\n {}\r\n}\r\n<\/pre>\nThe PrintWinnes<\/tt> function would have to change to:<\/p>\n\r\npublic void PrintWinners()\r\n{\r\n \/\/ creates a data context that takes the path of the database\r\n UCLDataContext dc = new UCLDataContext(@\"C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\UCL.mdf\");\r\n\r\n \/\/ creates a sequence of winners ordered descending by the winning year\r\n var result = from w in dc.Winners\r\n orderby w.Year descending\r\n select w;\r\n\r\n \/\/ prints the sequence of winners\r\n foreach (var w in result)\r\n {\r\n Console.WriteLine(\"{0} {1}, {2}\",\r\n w.Year, w.Name, w.Country);\r\n }\r\n}\r\n<\/pre>\nOnly querying the database is not enough. DataContext<\/tt> also allow us to submit changes to the database.<\/p>\n
The following function shows how to add a winner to the table Winners:<\/p>\n
\r\npublic void AddWinner()\r\n{\r\n \/\/ creates a data context that takes the path of the database\r\n UCLDataContext dc = new UCLDataContext(@\"C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\UCL.mdf\");\r\n\r\n \/\/ adds a new winner to the table\r\n dc.Winners.Add(new Winner { Name = \"Manchester United\", Country = \"England\", Year = 2007});\r\n\r\n \/\/ submites the changes\r\n dc.SubmitChanges();\r\n}\r\n<\/pre>\nIf you want for instance to remove all the winners from Spain, we can do the following:<\/p>\n
\r\npublic void DeleteWinner()\r\n{\r\n \/\/ creates a data context that takes the path of the database\r\n UCLDataContext dc = new UCLDataContext(@\"C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\UCL.mdf\");\r\n\r\n \/\/ remove a sequence of winners\r\n dc.Winners.RemoveAll(from w in dc.Winners\r\n where w.Country == \"Spain\"\r\n select w);\r\n\r\n \/\/ submites the changes\r\n dc.SubmitChanges();\r\n}\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"LINQ to SQL is an API that allows querying relational databases. The samples in this post are based on the concepts of: Entity classes (which are instances of Entity Types). Such a class is a regular .NET class that is decorated with the attribute Table and its properties and fields with the attribute Column DataContext, … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_uag_custom_page_level_css":"","advgb_blocks_editor_width":"","advgb_blocks_columns_visual_guide":"","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[11,14],"tags":[454,451,328,279],"class_list":["post-37","post","type-post","status-publish","format-standard","hentry","category-csharp","category-linq","tag-net","tag-c","tag-linq-2","tag-sql"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t