With the advent of schema-less databases, things have become a bit more interesting in how you query the data. Most NoSQL databases invented their own query languages. NosDB – a native .NET NoSQL database on the other hand uses standard SQL to make you feel right at home. But wait! There is another way that you can query your data, and that is through LINQ.
LINQ helps you reduce the amount of code you write and is also easy to learn. It helps you bridge the gulf between strongly typed programming languages and databases. More importantly, LINQ helps you to be more productive.
What exactly is LINQ?
Language Integrated Query (LINQ) is one of the features introduced by Microsoft in .NET framework 3.5 and is now widely used in the industry due to its tidiness and easy-to-learn patterns. I have seen many developers skip the ‘learning SQL’ phase and move directly to learning LINQ. The beauty of LINQ is that it gives you the illusion that you are just working with an object instead of working with SQL queries. LINQ works with any collection of objects that support IEnumerable. Many data management products come with LINQ integration.
Accessing a NoSQL Database through LINQ
NosDB provides extensive and easy to use LINQ integration. The NosDB LINQ provider basically converts LINQ queries into SQL. These SQL queries are then sent over to the NosDB server. NosDB then executes those SQL queries and returns the result.
To enable NosDB LINQ provider in your application, all you have to do is include the LINQ provider in your project and then refer the
Alachisoft.NosDB.Client.Linq library. Next, in order to execute a LINQ query on a NosDB collection, you retrieve an
IQueryable object, from the collection you want to query, using the
AsQueryable method. And finally, you perform your required LINQ queries as you normally would. The data will be directly fetched from the database.
To demonstrate, in the following example I performed a
SELECT query on the products collection to fetch all those products with a unit-price less than 20.
//Initializing Database and Collection
string connectionString = "Data Source=220.127.116.11; " +
"Port=8800; " +
"Local Instance=False; " +
Database _database = NosDB.GetDatabase(connectionString);
DBCollection<Product> _products = _database.GetDBCollection<Product>("Product");
_products = _database.GetDBCollection<Product>("Product");
IQueryable<Product> products =_products.AsQueryable();
// SELECT * FROM Product WHERE UnitPrice < 20
var results = products.Select(p=>p).Where(p => p.UnitPrice < 20).ToList<Product>();
foreach(var result in results)
NosDB LINQ Extension Methods
In addition to
SELECT queries, LINQ for NosDB also supports data-manipulation statements which include
INSERT using NosDB Extensions. In order to do this, you get the
IQueryable object from the collection by using the
AsUpdate method as explained below in the example.
DBUpdatable<Product> updateableProducts = _products.AsUpdateable();
// UPDATE Product SET (Category = “New Category”) WHERE Category =”Old Category”
DBUpdateBuilder update = DBUpdate.Set("Category", "NewCategory");
long RowsAffected =
updateableProducts.Update(update, p => p.Category == "Old Category", WriteConcern.Journal);
And for removal, the Delete method takes in the document instance to be deleted, along with an optional
WriteConcern value. By default, the
WriteConcern is set to
// DELETE FROM Products WHERE ProductID>10
long RowsAffected = updateableProducts.Delete(p => p.ProductID > 10, WriteConcern.InMemory);
To see how LINQ is generating SQL queries at the backend, there’s a small trick. Use the
ToString method at the end of the LINQ query like so:
String sqlQuery = products.OrderBy(p => p.Category).Select(x => x).ToString();
Doing so for the above LINQ query will output the following string:
SELECT * FROM Product ORDER BY Category
Does LINQ replace SQL?
No, not at all. There are still places where you would prefer writing plain SQL over LINQ despite its advantages. Scenarios where SQL would be preferred include:
- When hand tweaking is required for slow performing queries.
- When you want to use UDFs to perform some calculations on the server side. UDFs are only accessible through SQL.
- Also, there is also no support in .NET LINQ to manage any entity. Therefore, to administer your database or your NosDB cluster you have no other choice than to perform SQL queries. These queries fall under the categories of DDL and DCL.
In conclusion, NosDB gives you the choice of querying data using LINQ and/or SQL depending on your preference.
To learn more about capabilities of LINQ in NosDB, please refer to the NosDB LINQ documentation.