Here, I will discuss about basic LinQ to SQL tutorial overview, then how to get started very quickly with its usage on our C#.NET based applications(This can be used in both desktop and web applications in the exactly same way). Besides, I will also discuss about using ‘select’ query in linq to retrieve data and traverse through the query results(in case of multiple result objects). To understand this tutorial properly, You should have handy knowledge on c# and have visual studio 2008+(.NET Framework 3.0+ ) to successfully able to run LINQ To SQL examples.
What Is LINQ To SQL?
Full meaning of LINQ is ‘Language Integrated Query’, which replaces the traditional sql query execution process. Moreover, it doesn’t only applicable to manipulate database results, but it can also be used to manipulates array/list collections. LinQ was released as part of the .NET framework 3.0 and can be used from languages supported by .NET framework like C#, VB etc. The term ‘LINQ To SQL‘ refers to the technology by which we can use LINQ for access SQL Databases. Here in this tutorial, I will show step by step ways to get started with LINQ To SQL programming with C#.
Read All LinQ Tutorials By CodeSamplez.com
Mapping LINQ To SQL Class From SQL Server Database:
First step to be able to use LinQ on our SQL database, we will need to define a way, by which .NET can recognize the database as Classes/Objects, so we will need to map the database tables/stored procedures to LinQ to SQL classes. To accomplish this task successfully, first open your project in the solution explorer, right click->add->new item, in the ‘data’ categories, there is a type named ‘LINQ To SQL Classes’. Select that. We will get a .dbml file created along with designer interface.
The designer interface has two-part, one for dragging tables from server explorer(to create classes from tables automatically), another is for methods where we can drag stored procedures etc. After dragging all classes from server explorer, we are done. Here is a sample db structure that we will be using on the way of this tutorial:
Select Data Using LinQ To SQL:
After we make the DBML files appropriately, it’s very simple to get started our actually implementation in c# code. Most interesting thing is, we will be using sql query like syntax right from the c# code for getting database results. Suppose, we are trying to validate a user against a given username/password from database. Here is a sample codes of a function for such purpose:
public bool IsValidUser(string userName, string passWord) { DBNameDataContext myDB = new DBNameDataContext(); var userResults = from u in myDB.Users where u.Username == userName && u.Password == passWord select u; return Enumerable.Count(userResults) > 0; }
You can see, the syntax is much like sql, but not exactly same though. First, when you create a new dbml file with name ‘DBName.dbml’, there is created a corresponding DataContext class in .net and named something like ‘DBNameDataContext’ (These DataContext classes are now responsible for .NET To Database communications) . In the linq query syntax, this object will be used to present the database.
Next, whenever we write something like “from u in myDB.Users” visual studio automatically treat ‘u’ as an object of User class, that actually represents database’s ‘users’ table(you will might also notice if your database table contains plural form like ‘users’, it automatically makes it as singular when creating the linq to sql class like ‘User’, though table name used in the query will still be plural).
Next, notice one of the most useful advantage of LINQ, if you make any kind of data type mistake in your code, you will be notified immediately while compiling your project. This will saves lots of your debugging time and lessen the db errors at the same time. its possible here as now all you are using are acting like a .NET class, so its simply validating the property’s data type. Of course, you will have to remember that, if you change your db structure/column data type etc later on, you should have to drag the tables from server explorer to dbml once again to reflect your db changes to the linq to sql classes.
Next, Note the result is assigned to a variable of type ‘var‘. This data type is also new from .NET framework 3.0 and used to represent data with dynamic types. That means, here any kind of data returned from the linq query will be assigned to that variable and you will have to just cast that to the proper data type.
Next, “Enumerable.Count“, this function count the number of rows(Or number of objects) returned by the query. You can use this function on ‘var’ type result object without need of casting it to any intermediate form.
Select Operation Without SQL Syntax in LinQ:
The above example showed how to use LinQ To SQL syntax for querying database for retrieve data. However, there is alternative simple ways also for avoid using query like syntax by using integrated ‘Where’ method. Here is a simple code example to accomplish that:
public bool IsValidUser(string userName, string passWord) { DBNameDataContext myDB = new DBNameDataContext(); List<User> users = myDB.Users.Where(u => u.Username == userName && u.Password==passWord); if(users.Count>0) { return true; } return false; }
Retrieve A Single Row With LinQ:
On the above example, we have learned to execute a SQL like statement. However, LINQ provides much more flexibility than that. Like, if you need a single item/row from database table, it can be done very easily. Here is a code sample for such cases:
public User GetUser(string userName) { DBNameDataContext myDB = new DBNameDataContext(); User user = myDB.Users.Single(u, u.UserName=>userName); return user; }
The above example will return a single record from database table. In the “u.UserName=>userName” part, you can mention any column name you want to be validated.
‘Foreach’ Loop Through All LinQ To SQL Returned Results:
In case, when LinQ query returns multiple results, we often need to traverse through all the result rows(here all LinQ objects) and process in some way. It can be done very easily with a foreach loop. Although for loop also can be used, however foreach is better in performance(to know details, you can refer to my for vs foreach article). here is the code samples for using the foreach loop for traversing through all result objects:
foreach(User user in userResults) { //checking the result as like object if(user.Role == 'admin') { //do whatever you need } }
References:
I have tried to give you a very basic overview for getting started with LINQ To SQL. However, in real application, you will be needed to know much more details. Here are some resource links, where you will get some more useful resources to enrich your knowledge base with in LINQ:
I will try to post more articles about some more advanced LinQ to SQL tutorials. Wish you a very good journey with LINQ programming. Happy coding 🙂
Thank you for the short tutorial. It’s very helpful.
Keep up the good work! very good tutorial, i help me a lot
Rana
Very clear and simple, very helpful. Thank you !
Really good attempt….. It hepl a lot,.
I would like to use a sql view to get the data instead of a table in c#. How I can do it? Thanks.
I didn’t get your point. Can you please explain a little more?
Simply drag the view onto the (left part of the) designer instead of the table. That should do the trick.
br johannes
excellent descrption
i need to use sql tables and sp’s witout adding it in SqltoClasses. It is possible using only dbContext.
Excellent
Really helpful ! It helps me a lot to getting basic of Linq. Thanks again
Hello sir, you have not mention where i have write the above code after add table in linq
Wherever you add it, doesn’t matter, as long as you have its namespace added to your .cs file you are writing.
Very nice i wanted to lean this for a long time now. 🙂
Thank you so much!
http://www.codeducky.org/sql-queries-in-linq/ is a post that explains how to convert common SQL queires, (e.g. queries with HAVING clauses, WHERE IN clause, CASE statement, etc.) into LINQ. It may be useful for developers who are learning LINQ.
Nice Articles..
very nice
Please tell me how to insert a new record in database using linq to sql ????
You will may like to checkout my other linq crud operations tutorial for that. Hope that helps.
Great Bro,
Thanks and keep it up.
Tried to learn LINQ a while ago but didn’t make sense but I am doing again and thanks to your article its all sinking in nicely. Thank you
You examples have made clear to me concepts that so far have appeared very complex and mysterious. Thank You for excellent articles.
excellent article. Thank you so much!
Simply Great and nice…
Hi,
Will you please tell the significance of the code generated in the dbml file,
Reply
Excellent very easy thank you just I need !
what is that u. object ?
very nice
Nice One keep it up man (y)
Good Article
How long does it take to forget the
“if (condition)
return true;
return false;”
programming structure?
The “return condition;” is simplier and satisties the “one function-one return” criterion.
Precise and easy to understand…
Hope this will become better with lot of more examples.
Thank you .. really a good start…
Excellent article. Explained very well. It is the first article that shows you how to use Linq. But there is a trade-off. Can anyone explain why Linq is better than just sql?