Time Management

 

Linq linq EF

Below are 100 different examples of LINQ queries using Entity Framework in C#. These examples cover a wide range of operations including selecting, filtering, grouping, and joining data from a hypothetical database. Before running these commands, ensure you have a context class and entity classes set up. For the sake of this example, let's assume you have a context class called `AppDbContext` with entities `User`, `Post`, `Comment`, and `Category`. ### Basic Select Queries 1.

1. **Select All Users** 

   var users = context.Users.ToList();   

2. **Select Specific User** 

   var user = context.Users.FirstOrDefault(u => u.Id == 1);   

3. **Select User Names** 

   var userNames = context.Users.Select(u => u.Name).ToList();   

4. **Select Users with Condition** 

   var activeUsers = context.Users.Where(u => u.IsActive).ToList();   

5. **Select Users Ordered by Name** 

   var orderedUsers = context.Users.OrderBy(u => u.Name).ToList();   

### Filtering Data 6. **Filter Users by Age** 

   var usersOver30 = context.Users.Where(u => u.Age > 30).ToList();   

7. **Filter Posts by Category** 

   var techPosts = context.Posts.Where(p => p.Category.Name == "Technology").ToList();   

8. **Filter Comments by User** 

   var userComments = context.Comments.Where(c => c.UserId == 1).ToList();   

9. **Filter Posts by Date** 

   var recentPosts = context.Posts.Where(p => p.CreatedDate >= DateTime.Now.AddDays(-30)).ToList();   

10. **Filter Users by Multiple Conditions** 

    var specificUsers = context.Users.Where(u => u.IsActive && u.Age < 25).ToList();    

### Grouping Data 11. **Group Posts by Category** 

    var postsByCategory = context.Posts.GroupBy(p => p.Category.Name).ToList();    

12. **Count Users by Status** 

    var userCountByStatus = context.Users.GroupBy(u => u.IsActive)

        .Select(g => new { IsActive = g.Key, Count = g.Count() }).ToList();    

13. **Group Comments by Post** 

    var commentsGroupedByPost = context.Comments.GroupBy(c => c.PostId).ToList();    

14. **Aggregate Function: Count Posts by User** 

    var postCountByUser = context.Users.Select(u => new 

    {

        UserName = u.Name,

        PostCount = u.Posts.Count()

    }).ToList();    

15. **Group By and Order** 

    var orderedPostsByCategory = context.Posts.GroupBy(p => p.Category.Name)

        .OrderByDescending(g => g.Count()).ToList();    

### Joining Data 16. **Join Users and Posts** 

    var userPosts = from u in context.Users

                    join p in context.Posts on u.Id equals p.UserId

                    select new { u.Name, p.Title };    

17. **Join Users with Comments** 

    var userComments = from u in context.Users

                       join c in context.Comments on u.Id equals c.UserId

                       select new { u.Name, c.Content };    

18. **Join Posts with Categories** 

    var postsWithCategories = from p in context.Posts

                              join c in context.Categories on p.CategoryId equals c.Id

                              select new { p.Title, c.Name };    

                            SELECT u.Name, p.Title

                            FROM Users AS u

                            JOIN Posts AS p ON u.Id = p.UserId;

19. **Join with Multiple Conditions** 

    var userPostComments = from u in context.Users

                           join p in context.Posts on u.Id equals p.UserId

                           join c in context.Comments on p.Id equals c.PostId

                           select new { u.Name, p.Title, c.Content };   

SELECT u.Name, p.Title, c.Content

FROM Users AS u

JOIN Posts AS p ON u.Id = p.UserId

JOIN Comments AS c ON p.Id = c.PostId; 

20. **Group Join** 

    var usersWithPosts = context.Users.GroupJoin(context.Posts,

        u => u.Id, p => p.UserId,

        (user, posts) => new { user, Posts = posts.ToList() }).ToList();    

### Paging and Ordering 21. **Skip and Take for Pagination** 

    var pagedUsers = context.Users.OrderBy(u => u.Name).Skip(10).Take(5).ToList();    

22. **Order by Multiple Columns** 

    var orderedUsers = context.Users.OrderBy(u => u.LastName).ThenBy(u => u.FirstName).ToList();    

23. **Descending Order** 

    var recentPostsDesc = context.Posts.OrderByDescending(p => p.CreatedDate).ToList();    

24. **Get Top N Users** 

    var topUsers = context.Users.OrderByDescending(u => u.Score).Take(10).ToList();    

25. **Distinct Users** 

    var distinctUserNames = context.Users.Select(u => u.Name).Distinct().ToList();    

### Projection 26. **Anonymous Types** 

    var userProjections = context.Users.Select(u => new { u.Name, u.Age }).ToList();    

27. **Select Specific Fields** 

    var userFields = context.Users.Select(u => new UserDto { Name = u.Name, Email = u.Email }).ToList();

    28. **Select with Calculation** 

    var userScores = context.Users.Select(u => new { u.Name, Score = u.Points * 2 }).ToList();

    29. **Select with Subquery** 

    var userWithPostCount = context.Users.Select(u => new 

    {

        u.Name,

        PostCount = context.Posts.Count(p => p.UserId == u.Id)

    }).ToList();    

30. **Select with Conditional Logic** 

    var conditionalUsers = context.Users.Select(u => new 

    {

        u.Name,

        Status = u.IsActive ? "Active" : "Inactive"

    }).ToList();    

### Aggregate Functions 31. **Count Total Users** 

    var totalUsers = context.Users.Count();    

32. **Max Age of Users** 

    var maxAge = context.Users.Max(u => u.Age);    

33. **Min Age of Users** 

    var minAge = context.Users.Min(u => u.Age);    

34. **Average Age of Users** 

    var averageAge = context.Users.Average(u => u.Age);    

35. **Sum of Scores** 

    var totalScore = context.Users.Sum(u => u.Score);    

### Complex Queries 36. **Find Users with No Posts**

    var usersWithNoPosts = context.Users.Where(u => !u.Posts.Any()).ToList();    

37. **Find Posts with No Comments** 

    var postsWithNoComments = context.Posts.Where(p => !p.Comments.Any()).ToList();    

38. **Find Users with Specific Post Count** 

    var usersWithPostCount = context.Users.Where(u => u.Posts.Count() > 5).ToList();    

39. **Find Categories with Posts** 

    var categoriesWithPosts = context.Categories.Where(c => c.Posts.Any()).ToList();    

40. **Find Recent Users** 

    var recentUsers = context.Users.Where(u => u.CreatedDate >= DateTime.Now.AddMonths(-1)).ToList();    

### Using `Any`, `All`, and `Contains` 41. **Check if User Exists** 

    var userExists = context.Users.Any(u => u.Id == 1);    

42. **Check if Any Posts Exist for a User** 

    var hasPosts = context.Users.Any(u => u.Id == 1 && u.Posts.Any());    

43. **Check if All Users are Active** 

    var allActive = context.Users.All(u => u.IsActive);    

44. **Check if Category Contains Specific Post** 

    var categoryHasPost = context.Categories.Any(c => c.Posts.Any(p => p.Id == 1));    

45. **Check if User ID is in a List** 

    var userIds = new List<int> { 1, 2, 3 };

    var usersInList = context.Users.Where(u => userIds.Contains(u.Id)).ToList();    

### Using FirstOrDefault and SingleOrDefault 46. **Get First Active User** 

    var firstActiveUser = context.Users.FirstOrDefault(u => u.IsActive);    

47. **Get Single User by ID** 

    var singleUser = context.Users.SingleOrDefault(u => u.Id == 1);    

48. **Get First Post by User** 

    var firstPostByUser = context.Posts.FirstOrDefault(p => p.UserId == 1);    

49. **Get Single Comment by Content** 

    var specificComment = context.Comments.SingleOrDefault(c => c.Content == "Some content");    

50. **Get First Category with Posts** 

    var firstCategoryWithPosts = context.Categories.FirstOrDefault(c => c.Posts.Any());    

### Joining with Grouped Data 51. **Join and Group Posts by User** 

    var postsGroupedByUser = from p in context.Posts

                              join u in context.Users on p.UserId equals u.Id

                              group p by u.Name into g

                              select new { UserName = g.Key, PostCount = g.Count() };    

52. **Join and Count Comments by Post** 

    var commentsCountByPost = from p in context.Posts

                               join c in context.Comments on p.Id equals c.PostId into comments

                               select new { PostTitle = p.Title, CommentCount = comments.Count() };    

53. **Join Users and Count Their Posts** 

    var userPostCounts = from u in context.Users

                         join p in context.Posts on u.Id equals p.UserId into posts

                         select new { UserName = u.Name, PostCount = posts.Count() };    

54. **Join Categories and Count Posts** 

    var categoryPostCounts = from c in context.Categories

                             join p in context.Posts on c.Id equals p.CategoryId into posts

                             select new { CategoryName = c.Name, PostCount = posts.Count() };    

55. **Join Users and Comments** 

    var userCommentCounts = from u in context.Users

                            join c in context.Comments on u.Id equals c.UserId

                            group c by u.Name into g

                            select new { UserName = g.Key, CommentCount = g.Count() };    

### Using Distinct 56. **Distinct User Names** 

    var distinctUserNames = context.Users.Select(u => u.Name).Distinct().ToList();    

57. **Distinct Post Titles** 

    var distinctPostTitles = context.Posts.Select(p => p.Title).Distinct().ToList();    

58. **Distinct Categories** 

    var distinctCategories = context.Categories.Select(c => c.Name).Distinct().ToList();    

59. **Distinct Comments** 

    var distinctComments = context.Comments.Select(c => c.Content).Distinct().ToList();    

60. **Distinct Users with Posts** 

    var distinctUsersWithPosts = context.Posts.Select(p => p.User).Distinct().ToList();    

### Additional Aggregate Queries 61. **Count of Posts per User** 

    var postCounts = context.Users.Select(u => new 

    {

        u.Name,

        PostCount = u.Posts.Count()

    }).ToList();    

62. **Total Comments by Post** 

    var totalCommentsByPost = context.Posts.Select(p => new 

    {

        p.Title,

        CommentCount = p.Comments.Count()

    }).ToList();    

63. **Sum of User Points** 

    var totalPoints = context.Users.Sum(u => u.Points);    

64. **Average Score of Users** 

    var averageScore = context.Users.Average(u => u.Score);    

65. **Max Score of Users** 

    var maxScore = context.Users.Max(u => u.Score);    

### Using `Where` with Predicate 66. **Dynamic Filtering** 

    var minAge = 18;

    var filteredUsers = context.Users.Where(u => u.Age >= minAge).ToList();    

67. **Filter by Multiple Conditions Dynamically** 

    var filteredPosts = context.Posts.Where(p => p.IsPublished && p.CreatedDate > DateTime.Now.AddMonths(-1)).ToList();    

68. **Filter by User Role** 

    var adminUsers = context.Users.Where(u => u.Role == "Admin").ToList();    

69. **Filter Users by Registration Date** 

    var recentRegistrations = context.Users.Where(u => u.CreatedDate > DateTime.Now.AddDays(-30)).ToList();    

70. **Filter Comments by Length** 

    var shortComments = context.Comments.Where(c => c.Content.Length < 50).ToList();    

### Using `SelectMany` 71. **SelectMany for Nested Collections** 

    var allComments = context.Users.SelectMany(u => u.Posts.SelectMany(p => p.Comments)).ToList();

    72. **Flatten Posts and Comments** 

    var allPostComments = context.Posts.SelectMany(p => p.Comments, (post, comment) => new { post.Title, comment.Content }).ToList();    

73. **SelectMany with Additional Projection** 

    var userPostComments = context.Users.SelectMany(u => u.Posts.SelectMany(p => p.Comments, (post, comment) => new { UserName = u.Name, PostTitle = post.Title, comment.Content })).ToList();

    74. **SelectMany for Categories and Posts** 

    var categoryPosts = context.Categories.SelectMany(c => c.Posts.Select(p => new { c.Name, p.Title })).ToList();

    75. **SelectMany with Grouping** 

    var postsGroupedByUser = context.Users.SelectMany(u => u.Posts, (user, post) => new { user.Name, post.Title }).GroupBy(x => x.Name).ToList();    

### Using `ThenBy` for Multiple Ordering 76. **Order by Multiple Criteria** 

    var orderedUsers = context.Users.OrderBy(u => u.LastName).ThenBy(u => u.FirstName).ToList();

    77. **Order Posts by Category then Date** 

    var orderedPosts = context.Posts.OrderBy(p => p.Category.Name).ThenByDescending(p => p.CreatedDate).ToList();    

78. **Order Comments by User then Date** 

    var orderedComments = context.Comments.OrderBy(c => c.User.Name).ThenBy(c => c.CreatedDate).ToList();    

79. **Order Categories by Post Count** 

    var orderedCategories = context.Categories.OrderByDescending(c => c.Posts.Count()).ToList();

    80. **Order Users by Score then Name** 

    var orderedUsersByScore = context.Users.OrderByDescending(u => u.Score).ThenBy(u => u.Name).ToList();    

### Using `Take` and `Skip` 81. **Skip and Take for Large Data Sets** 

    var pagedUsers = context.Users.OrderBy(u => u.Name).Skip(20).Take(10).ToList();

    82. **First 5 Active Users** 

    var firstFiveActiveUsers = context.Users.Where(u => u.IsActive).Take(5).ToList();

    83. **Last 5 Posts** 

    var lastFivePosts = context.Posts.OrderByDescending(p => p.CreatedDate).Take(5).ToList();

    84. **Pagination Example** 

    var pageNumber = 2;

    var pageSize = 10;

    var paginatedUsers = context.Users.OrderBy(u => u.Name).Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList();    

85. **Take with Conditional Logic** 

    var topActiveUsers = context.Users.Where(u => u.IsActive).OrderByDescending(u => u.Score).Take(10).ToList();    

### Using `LastOrDefault` and `FirstOrDefault` 86. **Get Last User by Registration Date** 

    var lastRegisteredUser = context.Users.OrderByDescending(u => u.CreatedDate).FirstOrDefault();

    87. **Get Last Comment by User** 

    var lastCommentByUser = context.Comments.Where(c => c.UserId == 1).OrderByDescending(c => c.CreatedDate).FirstOrDefault();

    88. **Get Last Post by User** 

    var lastPostByUser = context.Posts.Where(p => p.UserId == 1).OrderByDescending(p => p.CreatedDate).FirstOrDefault();    

89. **Get Last Category with Posts** 

    var lastCategoryWithPosts = context.Categories.Where(c => c.Posts.Any()).OrderByDescending(c => c.CreatedDate).FirstOrDefault();    

90. **Using LastOrDefault with Condition** 

    var lastActiveUser = context.Users.Where(u => u.IsActive).OrderByDescending(u => u.CreatedDate).FirstOrDefault();    

### Advanced Queries 91. **Complex Filtering with Join** 

    var complexFilter = from u in context.Users

                        join p in context.Posts on u.Id equals p.UserId

                        where u.IsActive && p.CreatedDate > DateTime.Now.AddMonths(-3)

                        select u;    

92. **Nested Queries** 

    var usersWithRecentPosts = context.Users.Where(u => u.Posts.Any(p => p.CreatedDate > DateTime.Now.AddDays(-30))).ToList();    

93. **Dynamic Order By** 

    var orderedUsers = context.Users.AsQueryable();

    orderedUsers = orderedUsers.OrderBy(u => u.LastName);

    if (someCondition)

    {

        orderedUsers = orderedUsers.ThenBy(u => u.FirstName);

    }

    var result = orderedUsers.ToList();

    

94. **Using GroupBy with Having** 

    var userPostCounts = from u in context.Users

                         join p in context.Posts on u.Id equals p.UserId

                         group p by u.Name into g

                         where g.Count() > 5

                         select new { UserName = g.Key, PostCount = g.Count() };    

95. **Using Union** 

    var activeUsers = context.Users.Where(u => u.IsActive);

    var inactiveUsers = context.Users.Where(u => !u.IsActive);

    var allUsers = activeUsers.Union(inactiveUsers).ToList();    

### More Aggregate Functions 96. **Count Distinct Categories** 

    var distinctCategoryCount = context.Categories.Select(c => c.Name).Distinct().Count();

    97. **Count of Active Users** 

    var activeUserCount = context.Users.Count(u => u.IsActive);

    98. **Count of Comments per Post** 

    var commentsPerPostCount = context.Posts.Select(p => new 

    {

        p.Title,

        CommentCount = p.Comments.Count()

    }).ToList();

    

99. **Max Comments on a Post** 

    var maxComments = context.Posts.Max(p => p.Comments.Count());

    100. **Sum of All Users' Points** 

    var totalUserPoints = context.Users.Sum(u => u.Points);    

### Conclusion These examples cover a wide range of LINQ operations in C# using Entity Framework. You can use them as a reference to perform various data querying tasks using LINQ. Each of these commands assumes that the relevant entities and relationships are properly configured in your Entity Framework model. Adjust the entity and property names as necessary for your specific application.