codefather.pl

... by Tadeusz Wójcik

komentarze

NoSql No Problem - NoRM and MongoDB Tutorial - Querying

MongoDB offers a rich query environment with lots of features. Queries in MongoDB are represented as JSON-style objects, very much like the documents we actually store in the database. If you’re moving to MongoDB from a relational databases, you’ll find that many SQL queries translate easily to MongoDB’s document-based query language. This tutorial is based on MongoDB’s documentation and its goal is to show how easy is querying MongoDB  database using NoRM.

NoRM is a .NET driver which makes working with MongoDB a dream :) It provides strongly typed interface around Mongo queries, linq support and very fast serialization from MongoDB documents to .NET CLR objects and vice versa.

Firstly let’s look at our model:

public class Post
    {
        public int Id { get; set; }
        public string Body { get; set; }
        public string Title { get; set; }
        public string AuthorName { get; set; }
        public DateTime CreationDate { get; set; }
        public DateTime LastModifyDate { get; set; }
        public IList<string> Tags { get; set; }
        public IList<Comment> Comments { get; set; }
        public PostStatistics Statistics { get; set; }
    }
public class PostStatistics
    {
        public int ViewsCount { get; set; }
        public int VotesCount { get; set; }
    }
public class Comment
    {
        public string Body { get; set; }
        public string AuthorName { get; set; }
    }


In database posts collection is represented as document collection - let’s look at first two posts from that collection (notice JSON notation):

                             //first post document
 { "_id" : 19,
   "Body" : "Body19",
   "Title" : "Title19", 
   "AuthorName" : "AuthorName 19",
   "CreationDate" : "Mon Nov 01 2010 00:00:00 GMT+0100 ",
   "LastModifyDate" : "Mon Nov 01 2010 00:00:00 GMT+0100 ",
   "Tags" : [ "python", "c++", "c#" ],
   "Comments" : [
                 {
                  "Body" : "Body1",
                  "AuthorName" : "AuthorName1"
                 },
                 {
                  "Body" : "Body2",
                  "AuthorName" : "AuthorName2"
                 }
                ],
   "Statistics" : { "ViewsCount" : 1, "VotesCount" : 1 } }

                           //second post document
{ "_id" : 20,
  "Body" : "Body20",
  "Title" : "Title20",
  "AuthorName" : "AuthorName 20",
  "CreationDate" : "Tue Nov 02 2010 00:00:00 GMT+0100 ",
  "LastModifyDate" : "Tue Nov 02 2010 00:00:00 GMT+0100",
  "Tags" : [ "java", "c++", "c#" ],
  "Comments" : [
                 {
                  "Body" : "some comment",
                  "AuthorName" : "AuthorName1"
                 },
                 {
                  "Body" : "Body2",
                  "AuthorName" : "AuthorName2"
                 }
                ],
  "Statistics" : { "ViewsCount" : 1223, "VotesCount" : 241 } }

Notice that comments and tags are embedded inside post document as well as PostStatistics.



All examples presented in this tutorial are available on github  and if looking for more check out NoRM Tests( also on github).

Every query in this tutorial is presented is three (if possible) forms:

  • MongoDB shell query (which you can execute in MongoDB shell)
  • NoRM anonymous object style query
  • NoRM Linq query 

Find

To return every document from the posts collection:

  • MongoDB shell:  
    db.posts.find({});
  • NoRM anonymous objects:  
    db.GetCollection<Post>().Find().ToList();
  • NoRM Linq:
    db.GetCollection<Post>().AsQueryable().ToList();

To find every post with title equals ‘Title 1’ and AuthorName : ‘Author 1’

  • MongoDB shell:  
    db.posts.find({Title: 'Title 1',AuthorName:'Author 1'});
  • NoRM anonymous objects:  
    db.GetCollection<Post>().Find(new{ Title="Title 1", AuthorName="Author 1"})
                                       .ToList();
  • NoRM Linq:
    db.GetCollection<Post>().AsQueryable()
                                       .Where(post=>post.Title=="Title 1" 
                                                  && post.AuthorName=="Author 1")
                                       .ToList();

To get post by Id:

  • MongoDB shell:  
    db.posts.find({ _id:1});
  • NoRM anonymous objects:  
    db.GetCollection<Post>().FindOne(new { Id = 1 });
  • NoRM Linq:
    db.GetCollection<Post>().AsQueryable().Single(p => p.Id == 1);

Retrieving a Subset of Fields

By default on a find operation, the entire document is returned, in MongoDB you can retrive subset of fields which is somewhat analogous to the list of column specifiers in a SQL SELECT statement (projection).  (Regardless of what field specifiers are included, the _id field is always returned).

If we just wanted to get the Body of posts with the VotesCount equal 3 , then we could issue this query:

  • MongoDB shell:  
    db.posts.find({'Statistics.VotesCount: 3'}, {Body: 1});
  • NoRM Linq:
    db.GetCollection<Post>().AsQueryable()
                                       .Where(p => p.Statistics.VotesCount == 3)
                                       .Select(p => p.Body).ToList();

To retrive views counts of posts with VotesCount equal 3 :

  • MongoDB shell:  
    db.posts.find({'Statistics.VotesCount': 3}, {'Statistics.ViewsCount': 1});
  • NoRM Linq:
    db.GetCollection<Post>().AsQueryable()
                                       .Where(p => p.Statistics.VotesCount == 3)
                                       .Select(p => p.Statistics.ViewsCount).ToList();

To retrive list of PostViewModels  where posts VotesCount is less than 3 

(projection to complex type):

  • MongoDB shell:  
    db.posts.find({'Statistics.VotesCount': 3},
                        {Body:1,CreationDate:1',Statistics.VotesCount': 1});
  • NoRM Linq:
    db.GetCollection<Post>().AsQueryable().Where(p => p.Statistics.VotesCount < 3)
                                                            .Select(p => new PostViewModel
                                                                {
                                                                   Body=p.Body,
                                                                   Id=p.Id,
                                                                   CreationDate=p.CreationDate,
                                                                   VotesCount=p.Statistics.VotesCount
                                                                }).ToList();

Sorting

MongoDB queries can return sorted results.

To return all documents sorted by Title  in ascending order, we’d query like so:

  • MongoDB shell:  
    db.posts.find({}).sort({Title:1});
  • NoRM anonymous objects:  
     db.GetCollection<Post>().Find(new { }, new { Title = OrderBy.Ascending }).ToList();
  • NoRM Linq:
    db.GetCollection<Post>().AsQueryable().OrderBy(p => p.Title).ToList();

Skip and Limit

MongoDB also supports skip and limit for easy paging.

Here we skip the first 20 posts, and limit our result set to 10 and also sort by Title:

  • MongoDB shell:  
    db.posts.find().skip(20).limit(10).sort({Title:1});
  • NoRM anonymous objects:  
     db.GetCollection<Post>().Find(new { }, new { Title = OrderBy.Ascending }, 20, 10)
                                      .ToList();
  • NoRM Linq:
     db.GetCollection<Post>().AsQueryable()
                                      .OrderBy(post => post.Title)
                                      .Skip(10)
                                      .Take(20).ToList();

To return 20 newest posts from posts collection:

  • MongoDB shell:  
    db.posts.find().limit(20).sort({CreationDate:-1});
  • NoRM anonymous objects:  
    db.GetCollection<Post>().Find(new{},new{CreationDate=OrderBy.Descending},20,0)
                                     .ToList();
  • NoRM Linq:
     db.GetCollection<Post>().AsQueryable()
                                     .OrderByDescending(post=>post.CreationDate)
                                     .Take(20).ToList();

Conditional Operators

To return all posts with VotesCount greater than 3:

  • MongoDB shell:  
    db.posts.find({'Statistics.VotesCount':{ $gt:3}});
  • NoRM Linq:
    db.GetCollection<Post>().AsQueryable()
                                     .Where(post => post.Statistics.VotesCount>3).ToList();

To return all posts with VotesCount greater than or equal 3:

  • MongoDB shell:  
    db.posts.find({'Statistics.VotesCount':{ $gte:3}});
  • NoRM Linq:
    db.GetCollection<Post>().AsQueryable()
                                     .Where(post => post.Statistics.VotesCount >= 3).ToList();

To return all posts with VotesCount less than  5:

  • MongoDB shell:  
    db.posts.find({'Statistics.VotesCount':{ $lt:5}});
  • NoRM Linq:
     db.GetCollection<Post>().AsQueryable()
                                      .Where(post => post.Statistics.VotesCount < 5)
                                      .ToList();

To return all posts with VotesCount less than or equal 5:

  • MongoDB shell:  
     db.posts.find({'Statistics.VotesCount':{ $lte:5}});
  • NoRM Linq:
      db.GetCollection<Post>().AsQueryable()
                                       .Where(post => post.Statistics.VotesCount <= 5)
                                       .ToList();

To return all posts with VotesCount greater than 3 and less than or equal 5 (range query):

  • MongoDB shell:  
      db.posts.find({'Statistics.VotesCount':{$gt:3, $lte:5}});
  • NoRM Linq:
    db.GetCollection<Post>().AsQueryable()
                                     .Where(post =>post.Statistics.VotesCount>3 &&
                                                post.Statistics.VotesCount <= 5)
                                       .ToList();

$ne

Use $ne for “not equals”.

To retrive all posts where ViewsCount not equals 3:

  • MongoDB shell:  
      db.posts.find({'Statistics.ViewsCount':{$ne:3}});
  • NoRM Linq:
    _postsInDb.Where(post => post.Statistics.ViewsCount != 3).ToList();

$in

The $in operator is analogous to the SQL IN modifier, allowing you to specify an array of possible matches.

To return all posts with tags ‘c#’ or ‘python’:

  • MongoDB shell:  
    db.posts.find({Tags:{$in:['c#','python']}});
  • NoRM anonymous objects:  
     db.GetCollection<Post>().Find(new {Tags = Q.In("python", "c#")}).ToList() ;

$nin

The $nin operator is similar to $in except that it selects objects for which the specified field does not have any value in the specified array.

To return all posts without tags ‘c#’ and ‘python’:

  • MongoDB shell:  
    db.posts.find({Tags:{$nin:['c#','python']}});
  • NoRM anonymous objects:  
    db.GetCollection<Post>().Find(new { Tags= Q.NotIn("c#", "python") })
                                     .ToList();

$all

The $all operator is similar to $in, but instead of matching any value in the specified array all values in the array must be matched.

To return all posts with tags ‘c#’ and ‘python’:

  • MongoDB shell:  
    dbdb.posts.find({Tags:{$all:['c#','python']}});
  • NoRM anonymous objects:  
     db.GetCollection<Post>().Find(new { Tags = Q.All("python", "c#") }).ToList();

$exists

Check for existence (or lack thereof) of a field.

To return posts where AuthorId property exists:

  • MongoDB shell:  
    db.posts.find({AuthorId:{$exists:true}});
  • NoRM anonymous objects:  
     db.GetCollection<Post>().Find(new { AuthorId = Q.Exists(true) }).ToList();

Currently $exists is not able to use an index. Indexes on other fields are still used.

$or

The $or operator lets you use a boolean or expression to do queries. You give $or a list of expressions, any of which can satisfy the query.

To return all posts with tag ‘c#’ or AuthorName ‘AuthorName1’:

  • MongoDB shell:  
     db.posts.find({$or:[ { AuthorName : "AuthorName1" } , { Tags : "c#" } ]});
  • NoRM anonymous objects:  
     db.GetCollection<Post>()
                          .Find(Q.Or(new {AuthorName="AuthorName1"},new {Tags="c#"}))
                          .ToList();

The $or operator retrieves matches for each or clause individually and eliminates duplicates when returning results.

Regular Expressions

You may use regexes in database query expressions.

To return all posts with title starting with ‘Title’:

  • MongoDB shell:  
    db.posts.find({Title:/^Title/});
  • NoRM Linq:
    db.GetCollection<Post>().AsQueryable()
                                     .Where(post => Regex.IsMatch(post.Title, "^Title"))
                                     .ToList();

Value in an Array

To look for value ‘python’ in Tags array:

  • MongoDB shell:  
    db.posts.find({Tags:"python"});
  • NoRM anonymous objects:  
     db.GetCollection<Post>().Find(new { Tags = "python" }).ToList();
  • NoRM Linq:
    db.GetCollection<Post>().AsQueryable()
                                     .Where(post => post.Tags.Any(x => x == "python"))
                                     .ToList();

$elemMatch

Use $elemMatch to check if an element in an array matches the specified match expression. Note that a single array element must match all the criteria specified.

To return all posts where Comments body is equal to ’ Body 1’ and AuthorName : ‘AuthorName1’ (single comment in Comments array must match both criteria):

  • MongoDB shell:  
    db.posts.find({Comments:{$elemMatch:{AuthorName:'Author 1',Body:'Body 1'}}});
  • NoRM anonymous objects:  
     db.GetCollection<Post>()
                           .Find(new {Comments= Q.ElementMatch(new Comment(){AuthorName = "Author 1",Body = "Body 1"}) })
                           .ToList();

count()

The count() method returns the number of objects matching the query specified. It is specially optimized to perform the count in the MongoDB server, rather than on the client side for speed and efficiency.

To return  posts Count where AuthorName equals ‘AuthorName1’:

  • MongoDB shell:  
     db.posts.find({AuthorName:"AuthorName1"}).count();
  • NoRM anonymous objects:  
     db.GetCollection<Post>().Count(new {AuthorName="AuthorName1"});

Array Element by Position

Array elements also may be accessed by specific array position:

  • MongoDB shell:  
    db.posts.find({Comments.1.AuthorName:'Author 1'});
  • NoRM linq:  
      db.GetCollection<Post>().AsQueryable()
                                      .Where(x => x.Comments[1].AuthorName == "Author 1")
                                      .ToList();

Distinct

The distinct command returns returns a list of distinct values for the given key across a collection. 

To return distinct tags  from post collection:

  • MongoDB shell:  
      db.posts.distict('Tags');
  • NoRM anonymous objects:  
      db.GetCollection<Post>().Distinct<string>("Tags");



This tutorial covers most querying capabilities of MongoDB (but not all of them - I didn’t covered map-reduce and few others).

If You want learn more, just check out:

I the future I’ll try to cover updating, deleting, and map-reduce in more depth.

kategorie:   #MongoDB#csharp#norm


Wrzuć linka u siebie: