C# & ASP.NET Topic
C# LINQ (Language Integrated Query)
1. LINQ Fundamentals
What is LINQ?
LINQ allows you to query data from various sources (collections, databases, XML, etc.) using a consistent, SQL-like syntax directly in C#.
Two Syntax Styles:
-
Query Syntax (SQL-like, more readable for simple queries)
-
Method Syntax (Lambda-based, more powerful and flexible)
2. LINQ with Collections - Basic Operations
Setting Up Our Data Models:
csharp
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public string Category { get; set; }
public decimal Price { get; set; }
public int Stock { get; set; }
public DateTime CreatedDate { get; set; }
}
public class Order
{
public int OrderId { get; set; }
public int CustomerId { get; set; }
public DateTime OrderDate { get; set; }
public decimal TotalAmount { get; set; }
public List<OrderItem> Items { get; set; } = new List<OrderItem>();
}
public class OrderItem
{
public int ProductId { get; set; }
public int Quantity { get; set; }
public decimal UnitPrice { get; set; }
}
// Sample data
List<Product> products = new List<Product>
{
new Product { Id = 1, Name = "Laptop", Category = "Electronics", Price = 999.99m, Stock = 10, CreatedDate = new DateTime(2023, 1, 15) },
new Product { Id = 2, Name = "Mouse", Category = "Electronics", Price = 29.99m, Stock = 50, CreatedDate = new DateTime(2023, 2, 20) },
new Product { Id = 3, Name = "Desk", Category = "Furniture", Price = 199.99m, Stock = 15, CreatedDate = new DateTime(2023, 3, 10) },
new Product { Id = 4, Name = "Chair", Category = "Furniture", Price = 149.99m, Stock = 20, CreatedDate = new DateTime(2023, 1, 5) },
new Product { Id = 5, Name = "Monitor", Category = "Electronics", Price = 299.99m, Stock = 8, CreatedDate = new DateTime(2023, 4, 25) },
new Product { Id = 6, Name = "Keyboard", Category = "Electronics", Price = 79.99m, Stock = 25, CreatedDate = new DateTime(2023, 2, 28) },
new Product { Id = 7, Name = "Bookshelf", Category = "Furniture", Price = 129.99m, Stock = 12, CreatedDate = new DateTime(2023, 3, 15) }
};
List<Order> orders = new List<Order>
{
new Order { OrderId = 1, CustomerId = 101, OrderDate = new DateTime(2023, 5, 1), TotalAmount = 1299.97m },
new Order { OrderId = 2, CustomerId = 102, OrderDate = new DateTime(2023, 5, 2), TotalAmount = 449.98m },
new Order { OrderId = 3, CustomerId = 101, OrderDate = new DateTime(2023, 5, 3), TotalAmount = 199.99m },
new Order { OrderId = 4, CustomerId = 103, OrderDate = new DateTime(2023, 5, 4), TotalAmount = 579.97m }
};
3. Query Syntax vs Method Syntax
Basic Filtering Examples:
Query Syntax:
csharp
// Get all electronics products
var electronicsQuery = from p in products
where p.Category == "Electronics"
select p;
// Get expensive electronics (> $100)
var expensiveElectronics = from p in products
where p.Category == "Electronics" && p.Price > 100
select p;
Method Syntax (Lambda Expressions):
csharp
// Get all electronics products
var electronicsMethod = products.Where(p => p.Category == "Electronics");
// Get expensive electronics (> $100)
var expensiveElectronicsMethod = products
.Where(p => p.Category == "Electronics" && p.Price > 100);
4. Filtering (Where Clause)
Detailed Filtering Examples:
Basic Filtering:
csharp
// Products with price between $50 and $200
var midRangeProducts = products.Where(p => p.Price >= 50 && p.Price <= 200);
// Products with low stock (< 15)
var lowStockProducts = products.Where(p => p.Stock < 15);
// Products created in 2023
var recentProducts = products.Where(p => p.CreatedDate.Year == 2023);
Complex Filtering with Multiple Conditions:
csharp
// Electronics with good stock and reasonable price
var goodDeals = products.Where(p =>
p.Category == "Electronics" &&
p.Stock > 5 &&
p.Price < 500);
// Products that start with specific letters
var productsStartingWithB = products.Where(p => p.Name.StartsWith("B"));
// Using String methods in filters
var shortNamedProducts = products.Where(p => p.Name.Length <= 5);
Filtering with Index (Method Syntax only):
csharp
// Get every second product
var everySecondProduct = products.Where((p, index) => index % 2 == 0);
// Get products with their positions
var productsWithPositions = products
.Select((p, index) => new { Product = p, Position = index + 1 })
.Where(x => x.Position <= 3); // First 3 products
5. Projection (Select Clause)
Projection transforms each element in the result set.
Basic Projection:
csharp
// Query Syntax - Select only product names
var productNamesQuery = from p in products
select p.Name;
// Method Syntax - Select only product names
var productNamesMethod = products.Select(p => p.Name);
// Select multiple properties into anonymous type
var productInfo = products.Select(p => new
{
p.Name,
p.Price,
p.Category
});
// Calculate values during projection
var productWithTax = products.Select(p => new
{
p.Name,
OriginalPrice = p.Price,
PriceWithTax = p.Price * 1.15m, // 15% tax
TaxAmount = p.Price * 0.15m
});
Advanced Projection Examples:
csharp
// Transform data with calculations
var productSummaries = products.Select(p => new
{
p.Name,
PriceCategory = p.Price < 100 ? "Budget" :
p.Price < 300 ? "Mid-range" : "Premium",
StockStatus = p.Stock == 0 ? "Out of Stock" :
p.Stock < 10 ? "Low Stock" : "In Stock",
DiscountPrice = p.Price * 0.9m // 10% discount
});
// Project with index
var productsWithRank = products
.Select((p, index) => new
{
Rank = index + 1,
p.Name,
p.Price
})
.OrderBy(x => x.Price);
6. Sorting (OrderBy)
Basic Sorting:
csharp
// Query Syntax - Sort by price ascending
var sortedByPriceQuery = from p in products
orderby p.Price
select p;
// Method Syntax - Sort by price ascending
var sortedByPriceMethod = products.OrderBy(p => p.Price);
// Sort by price descending
var sortedByPriceDesc = products.OrderByDescending(p => p.Price);
Multiple Field Sorting:
csharp
// Query Syntax - Sort by category, then by price descending
var multiSortQuery = from p in products
orderby p.Category, p.Price descending
select p;
// Method Syntax - Sort by category, then by price descending
var multiSortMethod = products
.OrderBy(p => p.Category)
.ThenByDescending(p => p.Price);
// Complex sorting logic
var customSorted = products
.OrderBy(p => p.Category)
.ThenBy(p => p.Stock > 10 ? 0 : 1) // In-stock first
.ThenBy(p => p.Price);
7. Grouping (GroupBy)
Basic Grouping:
csharp
// Query Syntax - Group by category
var groupedByCategoryQuery = from p in products
group p by p.Category into categoryGroup
select new
{
Category = categoryGroup.Key,
Products = categoryGroup,
Count = categoryGroup.Count()
};
// Method Syntax - Group by category
var groupedByCategoryMethod = products
.GroupBy(p => p.Category)
.Select(g => new
{
Category = g.Key,
Products = g,
Count = g.Count(),
TotalValue = g.Sum(p => p.Price * p.Stock)
});
Advanced Grouping Examples:
csharp
// Group by price range
var priceRangeGroups = products.GroupBy(p =>
p.Price < 100 ? "Budget ($0-100)" :
p.Price < 300 ? "Mid-range ($100-300)" : "Premium ($300+)")
.Select(g => new
{
PriceRange = g.Key,
Products = g.OrderBy(p => p.Price),
AveragePrice = g.Average(p => p.Price),
TotalProducts = g.Count()
});
// Group by multiple properties (using anonymous types)
var complexGrouping = products.GroupBy(p => new
{
p.Category,
IsExpensive = p.Price > 150
})
.Select(g => new
{
g.Key.Category,
g.Key.IsExpensive,
Products = g,
Count = g.Count()
});
8. Joining Data
Basic Joins:
csharp
// Let's create some order items
var orderItems = new List<OrderItem>
{
new OrderItem { ProductId = 1, Quantity = 1, UnitPrice = 999.99m },
new OrderItem { ProductId = 2, Quantity = 2, UnitPrice = 29.99m },
new OrderItem { ProductId = 5, Quantity = 1, UnitPrice = 299.99m },
new OrderItem { ProductId = 3, Quantity = 1, UnitPrice = 199.99m }
};
// Query Syntax - Join products with order items
var productOrdersQuery = from p in products
join oi in orderItems on p.Id equals oi.ProductId
select new
{
p.Name,
p.Category,
oi.Quantity,
TotalValue = oi.Quantity * oi.UnitPrice
};
// Method Syntax - Join products with order items
var productOrdersMethod = products.Join(orderItems,
p => p.Id, // Outer key selector
oi => oi.ProductId, // Inner key selector
(p, oi) => new // Result selector
{
p.Name,
p.Category,
oi.Quantity,
TotalValue = oi.Quantity * oi.UnitPrice
});
Group Joins (Left Joins):
csharp
// Query Syntax - Group Join (like LEFT JOIN in SQL)
var productsWithOrders = from p in products
join oi in orderItems on p.Id equals oi.ProductId into productOrders
from orderItem in productOrders.DefaultIfEmpty()
select new
{
p.Name,
OrderQuantity = orderItem?.Quantity ?? 0,
HasOrders = orderItem != null
};
// Method Syntax - Group Join
var productsWithOrdersMethod = products.GroupJoin(orderItems,
p => p.Id,
oi => oi.ProductId,
(p, orderItemsGroup) => new
{
Product = p,
OrderItems = orderItemsGroup,
TotalOrdered = orderItemsGroup.Sum(oi => oi.Quantity)
})
.SelectMany(
x => x.OrderItems.DefaultIfEmpty(),
(x, oi) => new
{
x.Product.Name,
OrderQuantity = oi?.Quantity ?? 0,
HasOrders = oi != null
});
9. Aggregation Operations
Common Aggregation Methods:
csharp
// Basic aggregations
var totalProducts = products.Count();
var totalElectronics = products.Count(p => p.Category == "Electronics");
var maxPrice = products.Max(p => p.Price);
var minPrice = products.Min(p => p.Price);
var averagePrice = products.Average(p => p.Price);
var totalStockValue = products.Sum(p => p.Price * p.Stock);
// Aggregations with grouping
var categoryStats = products
.GroupBy(p => p.Category)
.Select(g => new
{
Category = g.Key,
ProductCount = g.Count(),
AveragePrice = g.Average(p => p.Price),
MaxPrice = g.Max(p => p.Price),
MinPrice = g.Min(p => p.Price),
TotalStock = g.Sum(p => p.Stock)
});
Custom Aggregations:
csharp
// Aggregate - custom aggregation
var priceRange = products.Aggregate(
new { Min = decimal.MaxValue, Max = decimal.MinValue },
(acc, p) => new
{
Min = p.Price < acc.Min ? p.Price : acc.Min,
Max = p.Price > acc.Max ? p.Price : acc.Max
});
Console.WriteLine($"Price range: ${priceRange.Min} - ${priceRange.Max}");
// String aggregation
var allProductNames = products.Aggregate(
new StringBuilder(),
(sb, p) => sb.Append(sb.Length > 0 ? ", " : "").Append(p.Name),
sb => sb.ToString());
10. Element Operations
Accessing Specific Elements:
csharp
// First/Last operations
var firstProduct = products.First();
var firstElectronics = products.First(p => p.Category == "Electronics");
var lastProduct = products.Last();
var firstOrDefault = products.FirstOrDefault(p => p.Price > 1000); // Returns null if not found
// Single element (throws exception if multiple found)
var singleProduct = products.Single(p => p.Id == 1);
var singleOrDefault = products.SingleOrDefault(p => p.Id == 999); // Returns null if not found
// Element at position
var thirdProduct = products.ElementAt(2);
var secondToLast = products.ElementAt(products.Count - 2);
Safe Element Access:
csharp
// Safe operations that won't throw exceptions
var cheapProduct = products.FirstOrDefault(p => p.Price < 10);
if (cheapProduct != null)
{
Console.WriteLine($"Found cheap product: {cheapProduct.Name}");
}
else
{
Console.WriteLine("No cheap products found");
}
// Default values for empty collections
var defaultProduct = products.Where(p => p.Price > 1000)
.DefaultIfEmpty(new Product { Name = "No products" })
.First();
11. Set Operations
Working with Multiple Collections:
csharp
List<Product> newProducts = new List<Product>
{
new Product { Id = 8, Name = "Tablet", Category = "Electronics", Price = 399.99m, Stock = 15 },
new Product { Id = 2, Name = "Mouse", Category = "Electronics", Price = 29.99m, Stock = 50 }, // Duplicate
new Product { Id = 9, Name = "Lamp", Category = "Furniture", Price = 49.99m, Stock = 30 }
};
// Distinct - remove duplicates
var distinctCategories = products.Select(p => p.Category).Distinct();
// Union - combine collections (removes duplicates)
var allProducts = products.Union(newProducts, new ProductComparer());
// Intersect - common elements
var commonProducts = products.Intersect(newProducts, new ProductComparer());
// Except - elements in first but not in second
var uniqueToOriginal = products.Except(newProducts, new ProductComparer());
// Concat - combine collections (keeps duplicates)
var allProductsWithDuplicates = products.Concat(newProducts);
Custom Comparer for Complex Types:
csharp
public class Pro
ductComparer : IEqualityComparer<Product>
{
public bool Equals(Product x, Product y)
{
if (ReferenceEquals(x, y)) return true;
if (x is null || y is null) return false;
return x.Id == y.Id && x.Name == y.Name;
}
public int GetHashCode(Product obj)
{
return HashCode.Combine(obj.Id, obj.Name);
}
}
12. Partitioning (Paging)
Pagination Operations:
csharp
// Skip and Take for pagination
int pageSize = 3;
int pageNumber = 1;
var firstPage = products
.OrderBy(p => p.Id)
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize);
// Get top N most expensive products
var top3MostExpensive = products
.OrderByDescending(p => p.Price)
.Take(3);
// Skip while condition is true
var productsAfterMouse = products
.OrderBy(p => p.Id)
.SkipWhile(p => p.Name != "Mouse")
.Take(3);
Real Pagination Example:
csharp
public class PaginationService
{
public static (IEnumerable<Product> Page, int TotalPages) GetPage(
List<Product> allProducts,
int pageNumber,
int pageSize,
string sortBy = "Name")
{
// Apply sorting
var query = sortBy switch
{
"Price" => allProducts.OrderBy(p => p.Price),
"Name" => allProducts.OrderBy(p => p.Name),
"Category" => allProducts.OrderBy(p => p.Category),
_ => allProducts.OrderBy(p => p.Id)
};
// Calculate pagination
int totalCount = allProducts.Count;
int totalPages = (int)Math.Ceiling(totalCount / (double)pageSize);
var page = query
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.ToList();
return (page, totalPages);
}
}
// Usage
var (currentPage, totalPages) = PaginationService.GetPage(products, 1, 3, "Price");
Console.WriteLine($"Page 1 of {totalPages}:");
foreach (var product in currentPage)
{
Console.WriteLine($"{product.Name} - ${product.Price}");
}
13. Quantification Operations
Checking Conditions:
csharp
// Any - check if any elements satisfy condition
bool hasExpensiveProducts = products.Any(p => p.Price > 1000);
bool hasProducts = products.Any();
// All - check if all elements satisfy condition
bool allHavePrice = products.All(p => p.Price > 0);
bool allInStock = products.All(p => p.Stock > 0);
// Contains - check if collection contains specific element
var mouseProduct = products.First(p => p.Name == "Mouse");
bool containsMouse = products.Contains(mouseProduct, new ProductComparer());
Complex Conditions:
csharp
// Check business rules
bool canFulfillLargeOrder = products
.Where(p => p.Category == "Electronics")
.All(p => p.Stock >= 5);
bool hasLowStockItems = products.Any(p => p.Stock < 5);
// Validate data consistency
bool allProductsHaveValidPrices = products.All(p =>
p.Price > 0 && p.Price < 1000000);
bool hasDuplicates = products
.GroupBy(p => p.Id)
.Any(g => g.Count() > 1);
14. LINQ with Databases (Entity Framework)
Database Context and Models:
csharp
public class AppDbContext : DbContext
{
public DbSet<Product> Products { get; set; }
public DbSet<Order> Orders { get; set; }
public DbSet<Customer> Customers { get; set; }
}
// LINQ to Entities - queries are translated to SQL
public class ProductService
{
private readonly AppDbContext _context;
public ProductService(AppDbContext context)
{
_context = context;
}
public async Task<List<Product>> GetElectronicsUnderPriceAsync(decimal maxPrice)
{
// This gets translated to SQL and executed on the database
return await _context.Products
.Where(p => p.Category == "Electronics" && p.Price <= maxPrice)
.OrderBy(p => p.Price)
.ToListAsync();
}
public async Task<decimal> GetTotalInventoryValueAsync()
{
return await _context.Products
.SumAsync(p => p.Price * p.Stock);
}
public async Task<List<CategorySummary>> GetCategorySummariesAsync()
{
return await _context.Products
.GroupBy(p => p.Category)
.Select(g => new CategorySummary
{
Category = g.Key,
ProductCount = g.Count(),
AveragePrice = g.Average(p => p.Price),
TotalStock = g.Sum(p => p.Stock)
})
.OrderByDescending(cs => cs.TotalStock)
.ToListAsync();
}
}
public class CategorySummary
{
public string Category { get; set; }
public int ProductCount { get; set; }
public decimal AveragePrice { get; set; }
public int TotalStock { get; set; }
}
Complex Database Queries:
csharp
public class ReportingService
{
private readonly AppDbContext _context;
public async Task<List<SalesReport>> GetMonthlySalesReportAsync(int year)
{
return await _context.Orders
.Where(o => o.OrderDate.Year == year)
.GroupBy(o => new { o.OrderDate.Year, o.OrderDate.Month })
.Select(g => new SalesReport
{
Year = g.Key.Year,
Month = g.Key.Month,
TotalSales = g.Sum(o => o.TotalAmount),
OrderCount = g.Count(),
AverageOrderValue = g.Average(o => o.TotalAmount)
})
.OrderBy(r => r.Year)
.ThenBy(r => r.Month)
.ToListAsync();
}
public async Task<List<CustomerOrderHistory>> GetCustomerOrderHistoryAsync()
{
return await _context.Customers
.Select(c => new CustomerOrderHistory
{
CustomerId = c.Id,
CustomerName = c.Name,
TotalOrders = c.Orders.Count,
TotalSpent = c.Orders.Sum(o => o.TotalAmount),
LastOrderDate = c.Orders.Max(o => o.OrderDate),
FavoriteCategory = c.Orders
.SelectMany(o => o.Items)
.Join(_context.Products,
oi => oi.ProductId,
p => p.Id,
(oi, p) => p.Category)
.GroupBy(cat => cat)
.OrderByDescending(g => g.Count())
.Select(g => g.Key)
.FirstOrDefault()
})
.Where(co => co.TotalOrders > 0)
.OrderByDescending(co => co.TotalSpent)
.ToListAsync();
}
}
15. Performance Considerations and Best Practices
Deferred vs Immediate Execution:
csharp
public class ExecutionExamples
{
public static void DemonstrateExecution()
{
// Deferred Execution - query is not executed until enumerated
var deferredQuery = products.Where(p => p.Price > 100);
Console.WriteLine("Query defined but not executed");
// Execution happens here:
foreach (var product in deferredQuery)
{
Console.WriteLine(product.Name);
}
// Immediate Execution - query executes immediately
var immediateResult = products.Where(p => p.Price > 100).ToList();
var count = products.Count(p => p.Price > 100); // Executes immediately
var maxPrice = products.Max(p => p.Price); // Executes immediately
}
}
Performance Tips:
csharp
public class PerformanceTips
{
// Use AsNoTracking for read-only operations
public async Task<List<Product>> GetProductsReadOnlyAsync()
{
return await _context.Products
.AsNoTracking() // Better performance for read-only
.Where(p => p.Stock > 0)
.ToListAsync();
}
// Select only needed columns
public async Task<List<ProductInfo>> GetProductInfoAsync()
{
return await _context.Products
.Where(p => p.Category == "Electronics")
.Select(p => new ProductInfo
{
Id = p.Id,
Name = p.Name,
Price = p.Price
}) // Only select needed columns
.ToListAsync();
}
// Avoid N+1 queries
public async Task<List<CustomerWithOrders>> GetCustomersWithOrdersAsync()
{
// GOOD: Single query with Include
return await _context.Customers
.Include(c => c.Orders) // Eager loading
.Select(c => new CustomerWithOrders
{
Customer = c,
OrderCount = c.Orders.Count,
TotalSpent = c.Orders.Sum(o => o.TotalAmount)
})
.ToListAsync();
}
}
Key LINQ Concepts Summary
-
Query Syntax: SQL-like, good for simple queries
-
Method Syntax: Lambda-based, more powerful and flexible
-
Deferred Execution: Queries execute when enumerated
-
Lazy Evaluation: Elements are produced on demand
-
Composability: LINQ operations can be chained together
LINQ provides a consistent, type-safe way to query data from any source, making C# code more expressive and maintainable. The ability to use the same syntax for in-memory collections, databases, XML, and other data sources is incredibly powerful.
Comments (0)
No comments yet. Be the first to comment!