Interview preparation · Technical guide

LINQ Interview Questions and Answers

LINQ interview material spanning in-memory sequences, provider queries, expressions, performance, testing, and integration. Query translation and deferred execution are explicitly scoped.

Examples are independent teaching snippets and may require application types, imports, packages, schema, and configuration. Framework behavior is version-dependent. Corrections address identified issues; the complete source code collection has not been compiled or integration-tested.

1. Difference between LINQ to Objects and LINQ to SQL

LINQ to Objects runs operators over IEnumerable<T> in the current process. LINQ to SQL is a legacy relational provider; modern applications commonly use EF Core or another provider. Provider behavior is not defined by LINQ alone: translation, null semantics, and supported methods depend on the provider.

2. LINQ Deferred Execution

Many LINQ operators use deferred execution: the query runs when enumerated, potentially again on each enumeration. Materialize with ToList/ToArray when you need a stable snapshot or must avoid repeated work. Deferred execution does not mean every operator streams; OrderBy and GroupBy must buffer. Reference: Deferred execution.

3. Different LINQ Providers

// 1. LINQ to Objects
var list = new List<int> { 1, 2, 3 };
var result1 = list.Where(x => x > 1);

// 2. LINQ to SQL
var dbResult = context.Products.Where(p => p.Price > 100);

// 3. LINQ to XML
XDocument doc = XDocument.Load("data.xml");
var xmlResult = doc.Descendants("item").Where(x => x.Attribute("id").Value == "1");

// 4. LINQ to Entities (Entity Framework)
var efResult = dbContext.Users.Where(u => u.IsActive);

// 5. LINQ to DataSet
DataSet ds = new DataSet();
var dsResult = ds.Tables["Users"].AsEnumerable()
    .Where(row => row.Field<string>("Name") == "John");

4. Custom LINQ Providers

public class CustomQueryProvider : IQueryProvider
{
    public IQueryable CreateQuery(Expression expression)
    {
        return new CustomQueryable(this, expression);
    }

    public IQueryable<TElement> CreateQuery<TElement>(Expression expression)
    {
        return new CustomQueryable<TElement>(this, expression);
    }

    public object Execute(Expression expression)
    {
        // Translate expression to your custom logic
        return TranslateExpression(expression);
    }

    public TResult Execute<TResult>(Expression expression)
    {
        return (TResult)Execute(expression);
    }

    private object TranslateExpression(Expression expression)
    {
        // Custom translation logic
        return null;
    }
}

public class CustomQueryable<T> : IQueryable<T>
{
    private readonly CustomQueryProvider _provider;
    private readonly Expression _expression;

    public CustomQueryable(CustomQueryProvider provider, Expression expression)
    {
        _provider = provider;
        _expression = expression;
    }

    public Type ElementType => typeof(T);
    public Expression Expression => _expression;
    public IQueryProvider Provider => _provider;
    public IEnumerator<T> GetEnumerator() => _provider.Execute<IEnumerable<T>>(_expression).GetEnumerator();
    IEnumerator IEnumerable.GetEnumerator() => GetEnumerator();
}

5. IEnumerable vs IQueryable

IEnumerable<T> represents executable iteration over values. IQueryable<T> represents a provider-owned query expression; its provider may translate some operations to another language. Calling arbitrary .NET methods inside IQueryable predicates can fail translation or switch work to the client if materialized. Keep provider-translatable work before AsEnumerable/ToList.

6. LINQ Extension Methods

public static class LinqExtensions
{
    public static IEnumerable<T> WhereIf<T>(
        this IEnumerable<T> source, 
        bool condition, 
        Func<T, bool> predicate)
    {
        return condition ? source.Where(predicate) : source;
    }

    public static IEnumerable<T> DistinctBy<T, TKey>(
        this IEnumerable<T> source, 
        Func<T, TKey> keySelector)
    {
        var seenKeys = new HashSet<TKey>();
        return source.Where(element => seenKeys.Add(keySelector(element)));
    }

    public static IEnumerable<T> Shuffle<T>(this IEnumerable<T> source)
    {
        var random = new Random();
        return source.OrderBy(x => random.Next());
    }
}

// Usage
var numbers = new List<int> { 1, 2, 3, 4, 5 };
var filtered = numbers.WhereIf(true, n => n > 3);
var distinct = customers.DistinctBy(c => c.City);
var shuffled = numbers.Shuffle();

7. Performance Implications

Avoid repeated enumeration, accidental materialization, N+1 access patterns, and unnecessary sorting/grouping. For database queries, inspect generated SQL and plans; project narrowly and limit result size. PLINQ helps only for sufficiently costly independent CPU work and can reorder output. Reference: Efficient querying.

8. Handling Null Reference Exceptions

public static class SafeLinqExtensions
{
    public static IEnumerable<T> WhereNotNull<T>(this IEnumerable<T> source)
    {
        return source.Where(item => item != null);
    }

    public static IEnumerable<T> WhereNotNull<T, TResult>(
        this IEnumerable<T> source, 
        Func<T, TResult> selector)
    {
        return source.Where(item => item != null && selector(item) != null);
    }
}

// Usage
var customers = new List<Customer> { null, new Customer(), null, new Customer() };
var validCustomers = customers.WhereNotNull();

var names = customers
    .WhereNotNull(c => c.Name) // Safe null checking
    .Select(c => c.Name.ToUpper());

9. Method Syntax vs Query Syntax

var customers = GetCustomers();

// Method syntax
var methodResult = customers
    .Where(c => c.City == "London")
    .OrderBy(c => c.Name)
    .Select(c => new { c.Name, c.Email });

// Query syntax
var queryResult = from c in customers
                  where c.City == "London"
                  orderby c.Name
                  select new { c.Name, c.Email };

// Complex joins - Method syntax
var joinResult = customers.Join(
    orders,
    c => c.Id,
    o => o.CustomerId,
    (c, o) => new { Customer = c, Order = o });

// Complex joins - Query syntax
var joinQueryResult = from c in customers
                      join o in orders on c.Id equals o.CustomerId
                      select new { Customer = c, Order = o };

10. LINQ Expression Trees

public class ExpressionTreeExample
{
    public static Expression<Func<Customer, bool>> CreateFilter(string propertyName, object value)
    {
        var parameter = Expression.Parameter(typeof(Customer), "c");
        var property = Expression.Property(parameter, propertyName);
        var constant = Expression.Constant(value);
        var comparison = Expression.Equal(property, constant);

        return Expression.Lambda<Func<Customer, bool>>(comparison, parameter);
    }

    public static void Demo()
    {
        var customers = GetCustomers();

        // Create dynamic filter
        var filter = CreateFilter("City", "London");
        var filteredCustomers = customers.AsQueryable().Where(filter);

        // Compile for reuse
        var compiledFilter = filter.Compile();
        var filteredCustomers2 = customers.Where(compiledFilter);
    }
}

Query Operations

11. Complex LINQ Joins

// Multiple joins with complex conditions
var result = from c in customers
             join o in orders on c.Id equals o.CustomerId
             join od in orderDetails on o.Id equals od.OrderId
             join p in products on od.ProductId equals p.Id
             where c.City == "London" && o.OrderDate >= DateTime.Today.AddDays(-30)
             group new { c, o, od, p } by c.Name into g
             select new
             {
                 CustomerName = g.Key,
                 TotalOrders = g.Count(),
                 TotalAmount = g.Sum(x => x.od.Quantity * x.od.UnitPrice)
             };

// Left join using DefaultIfEmpty
var leftJoinResult = from c in customers
                     join o in orders on c.Id equals o.CustomerId into customerOrders
                     from co in customerOrders.DefaultIfEmpty()
                     select new
                     {
                         Customer = c,
                         Order = co
                     };

12. Group By Operations

// Simple grouping
var groupedByCity = customers.GroupBy(c => c.City);

// Complex grouping with multiple keys
var groupedByCityAndCountry = customers
    .GroupBy(c => new { c.City, c.Country })
    .Select(g => new
    {
        City = g.Key.City,
        Country = g.Key.Country,
        Count = g.Count(),
        TotalRevenue = g.Sum(c => c.TotalRevenue)
    });

// Grouping with having clause
var highValueGroups = customers
    .GroupBy(c => c.City)
    .Where(g => g.Sum(c => c.TotalRevenue) > 100000)
    .Select(g => new
    {
        City = g.Key,
        TotalRevenue = g.Sum(c => c.TotalRevenue),
        CustomerCount = g.Count()
    });

13. Aggregation Functions

public class AggregationExamples
{
    public static void Demo()
    {
        var numbers = new List<int> { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };

        // Basic aggregations
        var sum = numbers.Sum();
        var average = numbers.Average();
        var min = numbers.Min();
        var max = numbers.Max();
        var count = numbers.Count();

        // Custom aggregation
        var customSum = numbers.Aggregate(0, (acc, num) => acc + num);
        var product = numbers.Aggregate(1, (acc, num) => acc * num);

        // Complex aggregation with objects
        var customers = GetCustomers();
        var stats = customers.Aggregate(
            new { TotalRevenue = 0.0, CustomerCount = 0, AvgRevenue = 0.0 },
            (acc, customer) => new
            {
                TotalRevenue = acc.TotalRevenue + customer.Revenue,
                CustomerCount = acc.CustomerCount + 1,
                AvgRevenue = (acc.TotalRevenue + customer.Revenue) / (acc.CustomerCount + 1)
            });
    }
}

14. Ordering and Sorting

// Basic ordering
var orderedCustomers = customers.OrderBy(c => c.Name);

// Multiple level ordering
var multiOrdered = customers
    .OrderBy(c => c.Country)
    .ThenBy(c => c.City)
    .ThenByDescending(c => c.Revenue);

// Custom comparer
public class CustomerComparer : IComparer<Customer>
{
    public int Compare(Customer x, Customer y)
    {
        if (x == null && y == null) return 0;
        if (x == null) return -1;
        if (y == null) return 1;

        return string.Compare(x.Name, y.Name, StringComparison.OrdinalIgnoreCase);
    }
}

var customOrdered = customers.OrderBy(c => c, new CustomerComparer());

15. Filtering and Projection

// Advanced filtering
var filteredCustomers = customers
    .Where(c => c.IsActive && c.Revenue > 10000)
    .Where(c => c.City != null && c.City.Length > 0)
    .Where(c => c.Orders.Any(o => o.OrderDate >= DateTime.Today.AddDays(-30)));

// Complex projection
var projectedData = customers
    .Select(c => new
    {
        CustomerId = c.Id,
        FullName = $"{c.FirstName} {c.LastName}",
        RevenueCategory = c.Revenue switch
        {
            > 100000 => "High",
            > 50000 => "Medium",
            _ => "Low"
        },
        RecentOrders = c.Orders
            .Where(o => o.OrderDate >= DateTime.Today.AddDays(-90))
            .Count()
    });

16. Set Operations

var set1 = new List<int> { 1, 2, 3, 4, 5 };
var set2 = new List<int> { 4, 5, 6, 7, 8 };

// Union
var union = set1.Union(set2); // 1, 2, 3, 4, 5, 6, 7, 8

// Intersection
var intersection = set1.Intersect(set2); // 4, 5

// Difference
var difference = set1.Except(set2); // 1, 2, 3

// Symmetric difference
var symmetricDiff = set1.Union(set2).Except(set1.Intersect(set2)); // 1, 2, 3, 6, 7, 8

// Custom comparer for complex objects
var customerUnion = customers1.Union(customers2, new CustomerIdComparer());

17. Partitioning Operations

var numbers = Enumerable.Range(1, 100);

// Take and Skip
var first10 = numbers.Take(10);
var skipFirst10 = numbers.Skip(10);
var page2 = numbers.Skip(10).Take(10); // Pagination

// TakeWhile and SkipWhile
var takeWhilePositive = numbers.TakeWhile(n => n > 0);
var skipWhileLessThan50 = numbers.SkipWhile(n => n < 50);

// Chunking (C# 8.0+)
var chunks = numbers.Chunk(10); // Splits into chunks of 10

// Custom chunking for older versions
public static IEnumerable<IEnumerable<T>> Chunk<T>(this IEnumerable<T> source, int size)
{
    using var enumerator = source.GetEnumerator();
    while (enumerator.MoveNext())
    {
        yield return ChunkIterator(enumerator, size);
    }
}

private static IEnumerable<T> ChunkIterator<T>(IEnumerator<T> enumerator, int size)
{
    do
    {
        yield return enumerator.Current;
    } while (--size > 0 && enumerator.MoveNext());
}

18. Element Operations

var numbers = new List<int> { 1, 2, 3, 4, 5 };

// Single element operations
var first = numbers.First();
var firstOrDefault = numbers.FirstOrDefault();
var last = numbers.Last();
var single = numbers.Single(x => x == 3);

// Safe element operations
var safeFirst = numbers.FirstOrDefault(x => x > 10); // Returns 0 if not found
var safeSingle = numbers.SingleOrDefault(x => x > 10); // Returns 0 if not found

// Element at specific position
var elementAt = numbers.ElementAt(2); // 3
var elementAtOrDefault = numbers.ElementAtOrDefault(10); // 0 if out of range

// Custom default values
var customDefault = numbers.FirstOrDefault(x => x > 10, -1); // Returns -1 if not found

19. Conversion Operations

var numbers = new List<int> { 1, 2, 3, 4, 5 };

// ToArray, ToList, ToDictionary
var array = numbers.ToArray();
var list = numbers.ToList();
var dict = numbers.ToDictionary(n => n, n => n * 2);

// ToLookup (grouping with immediate execution)
var customers = GetCustomers();
var lookup = customers.ToLookup(c => c.City);
var londonCustomers = lookup["London"];

// Cast and OfType
var objects = new List<object> { 1, "hello", 2, "world" };
var integers = objects.OfType<int>(); // 1, 2
var strings = objects.OfType<string>(); // "hello", "world"

// AsEnumerable, AsQueryable
var enumerable = numbers.AsEnumerable();
var queryable = numbers.AsQueryable();

20. Generation Operations

// Range
var range = Enumerable.Range(1, 10); // 1, 2, 3, ..., 10

// Repeat
var repeated = Enumerable.Repeat("Hello", 5); // "Hello" repeated 5 times

// Empty
var empty = Enumerable.Empty<int>();

// Custom generation
public static IEnumerable<int> GenerateFibonacci(int count)
{
    int a = 0, b = 1;
    for (int i = 0; i < count; i++)
    {
        yield return a;
        int temp = a;
        a = b;
        b = temp + b;
    }
}

// Infinite sequence
public static IEnumerable<int> InfiniteSequence()
{
    int i = 0;
    while (true)
    {
        yield return i++;
    }
}

// Usage with Take to limit infinite sequences
var first10Fibonacci = GenerateFibonacci(10);
var first100Numbers = InfiniteSequence().Take(100);

Performance Optimization (Questions 21-30)

21. How do you optimize LINQ query performance?

Optimize LINQ only after identifying the source and cost. For IEnumerable, focus on enumeration count, allocations, and algorithmic complexity. For IQueryable, examine generated queries, database indexes, round trips, and rows/columns returned. A compiled query is a targeted optimization, not a default requirement.

22. What are the best practices for LINQ memory usage?

Answer: Memory optimization in LINQ focuses on reducing allocations and managing object lifecycles.

// ❌ High memory usage - Materializing large collections
var allUsers = context.Users.ToList(); // Loads all users into memory
var filtered = allUsers.Where(u => u.IsActive).ToList();

// ✅ Low memory usage - Streaming with yield return
public static IEnumerable<User> GetActiveUsersStream(IQueryable<User> users)
{
    foreach (var user in users.Where(u => u.IsActive))
    {
        yield return user;
    }
}

// ✅ Using ValueTuple to reduce allocations
public static IEnumerable<(int Id, string Name)> GetUserInfo(IQueryable<User> users)
{
    return users.Select(u => (u.Id, u.Name));
}

// ✅ Using Span<T> for high-performance scenarios
public static void ProcessUsers(Span<User> users)
{
    for (int i = 0; i < users.Length; i++)
    {
        ProcessUser(ref users[i]);
    }
}

23. How do you implement LINQ query caching?

Answer: Query caching improves performance by storing frequently used query results.

public class QueryCacheService
{
    private readonly IMemoryCache _cache;
    private readonly TimeSpan _defaultExpiration = TimeSpan.FromMinutes(30);

    public QueryCacheService(IMemoryCache cache)
    {
        _cache = cache;
    }

    public async Task<IEnumerable<T>> GetOrSetAsync<T>(
        string cacheKey, 
        Func<Task<IEnumerable<T>>> queryFactory,
        TimeSpan? expiration = null)
    {
        if (_cache.TryGetValue(cacheKey, out IEnumerable<T> cachedResult))
        {
            return cachedResult;
        }

        var result = await queryFactory();
        var cacheOptions = new MemoryCacheEntryOptions
        {
            AbsoluteExpirationRelativeToNow = expiration ?? _defaultExpiration,
            SlidingExpiration = TimeSpan.FromMinutes(10)
        };

        _cache.Set(cacheKey, result, cacheOptions);
        return result;
    }
}

// Usage with dependency injection
public class UserService
{
    private readonly QueryCacheService _cacheService;
    private readonly MyDbContext _context;

    public async Task<IEnumerable<User>> GetActiveUsersAsync()
    {
        return await _cacheService.GetOrSetAsync(
            "active_users",
            async () => await _context.Users.Where(u => u.IsActive).ToListAsync()
        );
    }
}

24. How do you handle LINQ N+1 query problems?

N+1 is usually a data-access pattern, not a LINQ operator. In EF Core, use a projection that fetches required data, deliberate Include/split queries when appropriate, or a batch query keyed by parent ids. Lazy loading can conceal N+1 behavior, so profile actual SQL.

25. What are the performance implications of LINQ lazy loading?

Answer: Lazy loading can improve initial load times but may cause performance issues if not managed properly.

// ❌ Potential performance issues with lazy loading
public class OrderService
{
    public async Task ProcessOrdersAsync()
    {
        var orders = context.Orders.ToList(); // Load orders
        foreach (var order in orders)
        {
            // Each access triggers a database query
            var customer = order.Customer; // Lazy loading
            var items = order.Items; // Lazy loading
        }
    }
}

// ✅ Optimized with explicit loading
public class OptimizedOrderService
{
    public async Task ProcessOrdersAsync()
    {
        var orders = await context.Orders
            .Include(o => o.Customer)
            .Include(o => o.Items)
            .ToListAsync(); // Single query with all data

        foreach (var order in orders)
        {
            // No additional database queries
            var customer = order.Customer;
            var items = order.Items;
        }
    }
}

// ✅ Using projection to avoid lazy loading
public async Task<IEnumerable<OrderSummary>> GetOrderSummariesAsync()
{
    return await context.Orders
        .Select(o => new OrderSummary
        {
            OrderId = o.Id,
            CustomerName = o.Customer.Name,
            ItemCount = o.Items.Count,
            TotalAmount = o.Items.Sum(i => i.Price * i.Quantity)
        })
        .ToListAsync();
}

26. How do you implement LINQ async operations?

Answer: Async LINQ operations improve scalability by not blocking threads during I/O operations.

// ✅ Async LINQ with Entity Framework
public class AsyncUserService
{
    public async Task<IEnumerable<User>> GetActiveUsersAsync()
    {
        return await context.Users
            .Where(u => u.IsActive)
            .ToListAsync();
    }

    public async Task<User> GetUserByIdAsync(int id)
    {
        return await context.Users
            .FirstOrDefaultAsync(u => u.Id == id);
    }

    public async Task<int> GetActiveUserCountAsync()
    {
        return await context.Users
            .CountAsync(u => u.IsActive);
    }
}

// ✅ Async operations with custom data sources
public static class AsyncEnumerableExtensions
{
    public static async Task<List<T>> ToListAsync<T>(this IAsyncEnumerable<T> source)
    {
        var list = new List<T>();
        await foreach (var item in source)
        {
            list.Add(item);
        }
        return list;
    }

    public static async Task<T> FirstOrDefaultAsync<T>(this IAsyncEnumerable<T> source, Func<T, bool> predicate)
    {
        await foreach (var item in source)
        {
            if (predicate(item))
                return item;
        }
        return default(T);
    }
}

// ✅ Parallel processing with async
public async Task<IEnumerable<ProcessedData>> ProcessDataParallelAsync(IEnumerable<RawData> data)
{
    var tasks = data.Select(async item =>
    {
        await Task.Delay(100); // Simulate async processing
        return new ProcessedData { Id = item.Id, Processed = true };
    });

    return await Task.WhenAll(tasks);
}

27. How do you optimize LINQ database queries?

Answer: Database query optimization focuses on reducing the number of queries and improving their efficiency.

// ❌ Inefficient - Multiple queries
public async Task<List<OrderSummary>> GetOrderSummariesInefficientAsync()
{
    var orders = await context.Orders.ToListAsync();
    var summaries = new List<OrderSummary>();

    foreach (var order in orders)
    {
        var customer = await context.Customers.FirstAsync(c => c.Id == order.CustomerId);
        var itemCount = await context.OrderItems.CountAsync(i => i.OrderId == order.Id);

        summaries.Add(new OrderSummary
        {
            OrderId = order.Id,
            CustomerName = customer.Name,
            ItemCount = itemCount
        });
    }
    return summaries;
}

// ✅ Optimized - Single query with joins
public async Task<List<OrderSummary>> GetOrderSummariesOptimizedAsync()
{
    return await context.Orders
        .Join(context.Customers,
            order => order.CustomerId,
            customer => customer.Id,
            (order, customer) => new { order, customer })
        .GroupJoin(context.OrderItems,
            o => o.order.Id,
            item => item.OrderId,
            (o, items) => new OrderSummary
            {
                OrderId = o.order.Id,
                CustomerName = o.customer.Name,
                ItemCount = items.Count()
            })
        .ToListAsync();
}

// ✅ Using raw SQL for complex queries
public async Task<List<OrderSummary>> GetOrderSummariesWithRawSqlAsync()
{
    var sql = @"
        SELECT o.Id as OrderId, c.Name as CustomerName, COUNT(oi.Id) as ItemCount
        FROM Orders o
        INNER JOIN Customers c ON o.CustomerId = c.Id
        LEFT JOIN OrderItems oi ON o.Id = oi.OrderId
        GROUP BY o.Id, c.Name";

    return await context.Set<OrderSummary>()
        .FromSqlRaw(sql)
        .ToListAsync();
}

28. How do you handle LINQ query compilation?

Answer: Query compilation improves performance by pre-compiling frequently used queries.

public static class CompiledQueries
{
    // Compiled query for frequently used operations
    public static readonly Func<MyDbContext, int, IQueryable<User>> GetUsersByAge =
        EF.CompileQuery((MyDbContext context, int minAge) =>
            context.Users.Where(u => u.Age >= minAge && u.IsActive));

    // Compiled query with multiple parameters
    public static readonly Func<MyDbContext, string, DateTime, IQueryable<Order>> GetOrdersByCustomerAndDate =
        EF.CompileQuery((MyDbContext context, string customerName, DateTime startDate) =>
            context.Orders
                .Where(o => o.Customer.Name == customerName && o.OrderDate >= startDate)
                .Include(o => o.Items));

    // Compiled query for single result
    public static readonly Func<MyDbContext, int, User> GetUserById =
        EF.CompileQuery((MyDbContext context, int id) =>
            context.Users.FirstOrDefault(u => u.Id == id));
}

// Usage
public class UserService
{
    public async Task<IEnumerable<User>> GetAdultUsersAsync()
    {
        return await CompiledQueries.GetUsersByAge(context, 18).ToListAsync();
    }

    public async Task<User> GetUserAsync(int id)
    {
        return await Task.FromResult(CompiledQueries.GetUserById(context, id));
    }
}

29. How do you implement LINQ query optimization?

Answer: Query optimization involves analyzing and improving query performance through various techniques.

public class QueryOptimizer
{
    // Query analysis and optimization
    public static IQueryable<T> OptimizeQuery<T>(IQueryable<T> query)
    {
        // Add logging to analyze query performance
        var queryString = query.ToQueryString();
        Console.WriteLine($"Generated SQL: {queryString}");

        return query;
    }

    // Batch processing for large datasets
    public static async Task ProcessBatchAsync<T>(
        IQueryable<T> query, 
        int batchSize, 
        Func<IEnumerable<T>, Task> processor)
    {
        var totalCount = await query.CountAsync();
        var processed = 0;

        while (processed < totalCount)
        {
            var batch = await query
                .Skip(processed)
                .Take(batchSize)
                .ToListAsync();

            await processor(batch);
            processed += batchSize;
        }
    }

    // Query result caching with invalidation
    public static async Task<IEnumerable<T>> GetCachedQueryResultAsync<T>(
        string cacheKey,
        IQueryable<T> query,
        IMemoryCache cache,
        TimeSpan expiration)
    {
        if (cache.TryGetValue(cacheKey, out IEnumerable<T> result))
        {
            return result;
        }

        result = await query.ToListAsync();
        cache.Set(cacheKey, result, expiration);
        return result;
    }
}

30. What are the best practices for LINQ bulk operations?

Answer: Bulk operations require special handling to maintain performance with large datasets.

public class BulkOperationsService
{
    // Bulk insert with batching
    public async Task BulkInsertAsync<T>(IEnumerable<T> entities, int batchSize = 1000)
    {
        var batches = entities.Chunk(batchSize);

        foreach (var batch in batches)
        {
            context.Set<T>().AddRange(batch);
            await context.SaveChangesAsync();
        }
    }

    // Bulk update with raw SQL for performance
    public async Task BulkUpdateAsync(string tableName, string setClause, string whereClause)
    {
        var sql = $"UPDATE {tableName} SET {setClause} WHERE {whereClause}";
        await context.Database.ExecuteSqlRawAsync(sql);
    }

    // Bulk delete with batching
    public async Task BulkDeleteAsync<T>(Expression<Func<T, bool>> predicate, int batchSize = 1000)
    {
        var query = context.Set<T>().Where(predicate);
        var totalCount = await query.CountAsync();
        var deleted = 0;

        while (deleted < totalCount)
        {
            var batch = await query
                .Skip(deleted)
                .Take(batchSize)
                .ToListAsync();

            context.Set<T>().RemoveRange(batch);
            await context.SaveChangesAsync();
            deleted += batchSize;
        }
    }

    // Using SqlBulkCopy for maximum performance
    public async Task BulkInsertWithSqlBulkCopyAsync<T>(IEnumerable<T> entities)
    {
        using var connection = new SqlConnection(context.Database.GetConnectionString());
        await connection.OpenAsync();

        using var bulkCopy = new SqlBulkCopy(connection);
        bulkCopy.DestinationTableName = typeof(T).Name;

        var dataTable = ConvertToDataTable(entities);
        await bulkCopy.WriteToServerAsync(dataTable);
    }
}

Advanced Patterns (Questions 31-40)

31. How do you implement LINQ custom operators?

Answer: Custom operators extend LINQ functionality for domain-specific operations.

public static class CustomLinqOperators
{
    // Custom operator for pagination
    public static IQueryable<T> Paginate<T>(
        this IQueryable<T> source, 
        int page, 
        int pageSize)
    {
        return source.Skip((page - 1) * pageSize).Take(pageSize);
    }

    // Custom operator for conditional filtering
    public static IQueryable<T> WhereIf<T>(
        this IQueryable<T> source,
        bool condition,
        Expression<Func<T, bool>> predicate)
    {
        return condition ? source.Where(predicate) : source;
    }

    // Custom operator for dynamic sorting
    public static IQueryable<T> OrderByDynamic<T>(
        this IQueryable<T> source,
        string propertyName,
        bool ascending = true)
    {
        var parameter = Expression.Parameter(typeof(T), "x");
        var property = Expression.Property(parameter, propertyName);
        var lambda = Expression.Lambda(property, parameter);

        var methodName = ascending ? "OrderBy" : "OrderByDescending";
        var method = typeof(Queryable).GetMethods()
            .First(m => m.Name == methodName && m.GetParameters().Length == 2)
            .MakeGenericMethod(typeof(T), property.Type);

        return (IQueryable<T>)method.Invoke(null, new object[] { source, lambda });
    }

    // Custom operator for batch processing
    public static IEnumerable<IEnumerable<T>> Batch<T>(
        this IEnumerable<T> source,
        int batchSize)
    {
        using var enumerator = source.GetEnumerator();
        while (enumerator.MoveNext())
        {
            yield return GetBatch(enumerator, batchSize);
        }
    }

    private static IEnumerable<T> GetBatch<T>(IEnumerator<T> enumerator, int batchSize)
    {
        do
        {
            yield return enumerator.Current;
        }
        while (--batchSize > 0 && enumerator.MoveNext());
    }
}

32. How do you handle LINQ expression composition?

Answer: Expression composition allows building complex queries dynamically.

public class ExpressionComposer
{
    // Composing multiple expressions with AND logic
    public static Expression<Func<T, bool>> ComposeAnd<T>(
        params Expression<Func<T, bool>>[] expressions)
    {
        if (expressions.Length == 0)
            return x => true;

        if (expressions.Length == 1)
            return expressions[0];

        var parameter = Expression.Parameter(typeof(T), "x");
        var body = expressions[0].Body;

        for (int i = 1; i < expressions.Length; i++)
        {
            var visitor = new ParameterReplacer(expressions[i].Parameters[0], parameter);
            var newBody = visitor.Visit(expressions[i].Body);
            body = Expression.AndAlso(body, newBody);
        }

        return Expression.Lambda<Func<T, bool>>(body, parameter);
    }

    // Composing expressions with OR logic
    public static Expression<Func<T, bool>> ComposeOr<T>(
        params Expression<Func<T, bool>>[] expressions)
    {
        if (expressions.Length == 0)
            return x => false;

        if (expressions.Length == 1)
            return expressions[0];

        var parameter = Expression.Parameter(typeof(T), "x");
        var body = expressions[0].Body;

        for (int i = 1; i < expressions.Length; i++)
        {
            var visitor = new ParameterReplacer(expressions[i].Parameters[0], parameter);
            var newBody = visitor.Visit(expressions[i].Body);
            body = Expression.OrElse(body, newBody);
        }

        return Expression.Lambda<Func<T, bool>>(body, parameter);
    }
}

// Helper class for parameter replacement
public class ParameterReplacer : ExpressionVisitor
{
    private readonly ParameterExpression _oldParameter;
    private readonly ParameterExpression _newParameter;

    public ParameterReplacer(ParameterExpression oldParameter, ParameterExpression newParameter)
    {
        _oldParameter = oldParameter;
        _newParameter = newParameter;
    }

    protected override Expression VisitParameter(ParameterExpression node)
    {
        return node == _oldParameter ? _newParameter : base.VisitParameter(node);
    }
}

// Usage
public class UserService
{
    public async Task<IEnumerable<User>> SearchUsersAsync(UserSearchCriteria criteria)
    {
        var expressions = new List<Expression<Func<User, bool>>>();

        if (!string.IsNullOrEmpty(criteria.Name))
            expressions.Add(u => u.Name.Contains(criteria.Name));

        if (criteria.MinAge.HasValue)
            expressions.Add(u => u.Age >= criteria.MinAge.Value);

        if (criteria.IsActive.HasValue)
            expressions.Add(u => u.IsActive == criteria.IsActive.Value);

        var combinedExpression = ExpressionComposer.ComposeAnd(expressions.ToArray());
        return await context.Users.Where(combinedExpression).ToListAsync();
    }
}

33. How do you implement LINQ dynamic queries?

Answer: Dynamic queries allow building queries at runtime based on user input or configuration.

public class DynamicQueryBuilder
{
    // Building dynamic WHERE clauses
    public static IQueryable<T> BuildDynamicWhere<T>(
        IQueryable<T> source,
        Dictionary<string, object> filters)
    {
        var parameter = Expression.Parameter(typeof(T), "x");
        Expression combinedExpression = null;

        foreach (var filter in filters)
        {
            var property = Expression.Property(parameter, filter.Key);
            var value = Expression.Constant(filter.Value);
            var comparison = Expression.Equal(property, value);

            combinedExpression = combinedExpression == null 
                ? comparison 
                : Expression.AndAlso(combinedExpression, comparison);
        }

        if (combinedExpression != null)
        {
            var lambda = Expression.Lambda<Func<T, bool>>(combinedExpression, parameter);
            return source.Where(lambda);
        }

        return source;
    }

    // Building dynamic SELECT projections
    public static IQueryable<object> BuildDynamicSelect<T>(
        IQueryable<T> source,
        string[] properties)
    {
        var parameter = Expression.Parameter(typeof(T), "x");
        var propertyExpressions = properties.Select(p => Expression.Property(parameter, p));
        var anonymousType = CreateAnonymousType(properties);
        var constructor = anonymousType.GetConstructors()[0];
        var newExpression = Expression.New(constructor, propertyExpressions);
        var lambda = Expression.Lambda<Func<T, object>>(newExpression, parameter);

        return source.Select(lambda);
    }

    // Building dynamic ORDER BY clauses
    public static IQueryable<T> BuildDynamicOrderBy<T>(
        IQueryable<T> source,
        string propertyName,
        bool ascending = true)
    {
        var parameter = Expression.Parameter(typeof(T), "x");
        var property = Expression.Property(parameter, propertyName);
        var lambda = Expression.Lambda(property, parameter);

        var methodName = ascending ? "OrderBy" : "OrderByDescending";
        var method = typeof(Queryable).GetMethods()
            .First(m => m.Name == methodName && m.GetParameters().Length == 2)
            .MakeGenericMethod(typeof(T), property.Type);

        return (IQueryable<T>)method.Invoke(null, new object[] { source, lambda });
    }

    private static Type CreateAnonymousType(string[] propertyNames)
    {
        var properties = propertyNames.Select(name => 
            new { Name = name, Type = typeof(object) });

        return AnonymousTypeBuilder.CreateType(properties);
    }
}

// Usage
public class DynamicUserService
{
    public async Task<IEnumerable<User>> GetUsersWithDynamicFilterAsync(
        Dictionary<string, object> filters)
    {
        var query = context.Users.AsQueryable();
        query = DynamicQueryBuilder.BuildDynamicWhere(query, filters);
        return await query.ToListAsync();
    }
}

34. How do you handle LINQ query building?

Answer: Query building involves constructing complex queries step by step.

public class QueryBuilder<T>
{
    private IQueryable<T> _query;
    private readonly List<Expression<Func<T, bool>>> _whereConditions;
    private readonly List<(string property, bool ascending)> _orderByConditions;

    public QueryBuilder(IQueryable<T> source)
    {
        _query = source;
        _whereConditions = new List<Expression<Func<T, bool>>>();
        _orderByConditions = new List<(string property, bool ascending)>();
    }

    public QueryBuilder<T> Where(Expression<Func<T, bool>> predicate)
    {
        _whereConditions.Add(predicate);
        return this;
    }

    public QueryBuilder<T> OrderBy(string propertyName, bool ascending = true)
    {
        _orderByConditions.Add((propertyName, ascending));
        return this;
    }

    public QueryBuilder<T> Include<TProperty>(Expression<Func<T, TProperty>> navigationProperty)
    {
        _query = _query.Include(navigationProperty);
        return this;
    }

    public QueryBuilder<T> Skip(int count)
    {
        _query = _query.Skip(count);
        return this;
    }

    public QueryBuilder<T> Take(int count)
    {
        _query = _query.Take(count);
        return this;
    }

    public IQueryable<T> Build()
    {
        // Apply WHERE conditions
        foreach (var condition in _whereConditions)
        {
            _query = _query.Where(condition);
        }

        // Apply ORDER BY conditions
        for (int i = 0; i < _orderByConditions.Count; i++)
        {
            var (property, ascending) = _orderByConditions[i];
            var parameter = Expression.Parameter(typeof(T), "x");
            var propertyExpression = Expression.Property(parameter, property);
            var lambda = Expression.Lambda(propertyExpression, parameter);

            var methodName = i == 0 
                ? (ascending ? "OrderBy" : "OrderByDescending")
                : (ascending ? "ThenBy" : "ThenByDescending");

            var method = typeof(Queryable).GetMethods()
                .First(m => m.Name == methodName && m.GetParameters().Length == 2)
                .MakeGenericMethod(typeof(T), propertyExpression.Type);

            _query = (IQueryable<T>)method.Invoke(null, new object[] { _query, lambda });
        }

        return _query;
    }
}

// Usage
public class UserService
{
    public async Task<IEnumerable<User>> GetFilteredUsersAsync(
        string name = null,
        int? minAge = null,
        bool? isActive = null,
        string orderBy = "Name",
        bool ascending = true,
        int skip = 0,
        int take = 10)
    {
        var queryBuilder = new QueryBuilder<User>(context.Users)
            .Include(u => u.Orders)
            .Skip(skip)
            .Take(take);

        if (!string.IsNullOrEmpty(name))
            queryBuilder.Where(u => u.Name.Contains(name));

        if (minAge.HasValue)
            queryBuilder.Where(u => u.Age >= minAge.Value);

        if (isActive.HasValue)
            queryBuilder.Where(u => u.IsActive == isActive.Value);

        queryBuilder.OrderBy(orderBy, ascending);

        var query = queryBuilder.Build();
        return await query.ToListAsync();
    }
}

35. How do you implement LINQ query translation?

Answer: Query translation converts LINQ expressions to other query languages or formats.

public class QueryTranslator
{
    // Translating LINQ to SQL string
    public static string TranslateToSql<T>(IQueryable<T> query)
    {
        return query.ToQueryString();
    }

    // Translating LINQ to MongoDB query
    public static FilterDefinition<T> TranslateToMongoFilter<T>(
        Expression<Func<T, bool>> predicate)
    {
        var visitor = new MongoExpressionVisitor<T>();
        visitor.Visit(predicate);
        return visitor.BuildFilter();
    }

    // Translating LINQ to Elasticsearch query
    public static QueryContainer TranslateToElasticsearchQuery<T>(
        Expression<Func<T, bool>> predicate)
    {
        var visitor = new ElasticsearchExpressionVisitor<T>();
        visitor.Visit(predicate);
        return visitor.BuildQuery();
    }

    // Custom translation for specific domain
    public static string TranslateToCustomFormat<T>(
        IQueryable<T> query,
        Func<Expression, string> translator)
    {
        var expression = query.Expression;
        return translator(expression);
    }
}

// Example MongoDB expression visitor
public class MongoExpressionVisitor<T> : ExpressionVisitor
{
    private FilterDefinitionBuilder<T> _builder;
    private FilterDefinition<T> _currentFilter;

    public MongoExpressionVisitor()
    {
        _builder = Builders<T>.Filter;
    }

    protected override Expression VisitBinary(BinaryExpression node)
    {
        if (node.NodeType == ExpressionType.Equal)
        {
            var property = GetPropertyName(node.Left);
            var value = GetConstantValue(node.Right);
            _currentFilter = _builder.Eq(property, value);
        }
        else if (node.NodeType == ExpressionType.AndAlso)
        {
            var leftFilter = Visit(node.Left);
            var rightFilter = Visit(node.Right);
            _currentFilter = _builder.And(leftFilter, rightFilter);
        }

        return base.VisitBinary(node);
    }

    public FilterDefinition<T> BuildFilter()
    {
        return _currentFilter;
    }

    private string GetPropertyName(Expression expression)
    {
        // Implementation to extract property name
        return "";
    }

    private object GetConstantValue(Expression expression)
    {
        // Implementation to extract constant value
        return null;
    }
}

36. How do you handle LINQ query optimization?

Answer: Query optimization involves analyzing and improving query performance.

public class QueryOptimizer
{
    // Query performance analysis
    public static async Task<QueryAnalysisResult> AnalyzeQueryAsync<T>(
        IQueryable<T> query)
    {
        var stopwatch = Stopwatch.StartNew();
        var sql = query.ToQueryString();
        var result = await query.ToListAsync();
        stopwatch.Stop();

        return new QueryAnalysisResult
        {
            ExecutionTime = stopwatch.Elapsed,
            SqlQuery = sql,
            ResultCount = result.Count,
            EstimatedCost = EstimateQueryCost(sql)
        };
    }

    // Query plan analysis
    public static async Task<string> GetQueryPlanAsync<T>(IQueryable<T> query)
    {
        var sql = query.ToQueryString();
        // Implementation to get execution plan from database
        return await GetExecutionPlanFromDatabase(sql);
    }

    // Query optimization suggestions
    public static List<OptimizationSuggestion> GetOptimizationSuggestions<T>(
        IQueryable<T> query)
    {
        var suggestions = new List<OptimizationSuggestion>();
        var sql = query.ToQueryString();

        // Check for missing indexes
        if (ContainsTableScan(sql))
        {
            suggestions.Add(new OptimizationSuggestion
            {
                Type = SuggestionType.AddIndex,
                Description = "Consider adding indexes for better performance"
            });
        }

        // Check for N+1 queries
        if (ContainsMultipleQueries(sql))
        {
            suggestions.Add(new OptimizationSuggestion
            {
                Type = SuggestionType.UseInclude,
                Description = "Consider using Include() to avoid N+1 queries"
            });
        }

        return suggestions;
    }

    private static bool ContainsTableScan(string sql)
    {
        // Implementation to detect table scans
        return sql.Contains("TABLE SCAN");
    }

    private static bool ContainsMultipleQueries(string sql)
    {
        // Implementation to detect multiple queries
        return sql.Split("SELECT").Length > 2;
    }

    private static double EstimateQueryCost(string sql)
    {
        // Implementation to estimate query cost
        return sql.Length * 0.1; // Simple estimation
    }
}

public class QueryAnalysisResult
{
    public TimeSpan ExecutionTime { get; set; }
    public string SqlQuery { get; set; }
    public int ResultCount { get; set; }
    public double EstimatedCost { get; set; }
}

public class OptimizationSuggestion
{
    public SuggestionType Type { get; set; }
    public string Description { get; set; }
}

public enum SuggestionType
{
    AddIndex,
    UseInclude,
    OptimizeJoin,
    UsePagination
}

37. How do you implement LINQ query analysis?

Answer: Query analysis provides insights into query performance and behavior.

public class QueryAnalyzer
{
    // Performance profiling
    public static async Task<QueryProfile> ProfileQueryAsync<T>(
        IQueryable<T> query,
        string queryName = null)
    {
        var profile = new QueryProfile
        {
            QueryName = queryName ?? typeof(T).Name,
            Timestamp = DateTime.UtcNow
        };

        var stopwatch = Stopwatch.StartNew();

        // Measure compilation time
        var compilationStopwatch = Stopwatch.StartNew();
        var compiledQuery = query.Compile();
        compilationStopwatch.Stop();
        profile.CompilationTime = compilationStopwatch.Elapsed;

        // Measure execution time
        var executionStopwatch = Stopwatch.StartNew();
        var result = await query.ToListAsync();
        executionStopwatch.Stop();
        profile.ExecutionTime = executionStopwatch.Elapsed;

        stopwatch.Stop();
        profile.TotalTime = stopwatch.Elapsed;
        profile.ResultCount = result.Count;
        profile.SqlQuery = query.ToQueryString();

        return profile;
    }

    // Memory usage analysis
    public static async Task<MemoryProfile> AnalyzeMemoryUsageAsync<T>(
        IQueryable<T> query)
    {
        var initialMemory = GC.GetTotalMemory(false);
        var result = await query.ToListAsync();
        var finalMemory = GC.GetTotalMemory(false);

        return new MemoryProfile
        {
            InitialMemory = initialMemory,
            FinalMemory = finalMemory,
            MemoryUsed = finalMemory - initialMemory,
            ObjectCount = result.Count
        };
    }

    // Query complexity analysis
    public static QueryComplexity AnalyzeComplexity<T>(IQueryable<T> query)
    {
        var sql = query.ToQueryString();

        return new QueryComplexity
        {
            JoinCount = CountJoins(sql),
            WhereConditionCount = CountWhereConditions(sql),
            SubqueryCount = CountSubqueries(sql),
            ComplexityScore = CalculateComplexityScore(sql)
        };
    }

    private static int CountJoins(string sql)
    {
        return sql.Split(new[] { "JOIN", "join" }, StringSplitOptions.None).Length - 1;
    }

    private static int CountWhereConditions(string sql)
    {
        return sql.Split(new[] { "WHERE", "where" }, StringSplitOptions.None).Length - 1;
    }

    private static int CountSubqueries(string sql)
    {
        return sql.Split(new[] { "SELECT", "select" }, StringSplitOptions.None).Length - 1;
    }

    private static double CalculateComplexityScore(string sql)
    {
        var score = 0.0;
        score += CountJoins(sql) * 10;
        score += CountWhereConditions(sql) * 5;
        score += CountSubqueries(sql) * 15;
        return score;
    }
}

public class QueryProfile
{
    public string QueryName { get; set; }
    public DateTime Timestamp { get; set; }
    public TimeSpan CompilationTime { get; set; }
    public TimeSpan ExecutionTime { get; set; }
    public TimeSpan TotalTime { get; set; }
    public int ResultCount { get; set; }
    public string SqlQuery { get; set; }
}

public class MemoryProfile
{
    public long InitialMemory { get; set; }
    public long FinalMemory { get; set; }
    public long MemoryUsed { get; set; }
    public int ObjectCount { get; set; }
}

public class QueryComplexity
{
    public int JoinCount { get; set; }
    public int WhereConditionCount { get; set; }
    public int SubqueryCount { get; set; }
    public double ComplexityScore { get; set; }
}

38. How do you handle LINQ query debugging?

Answer: Query debugging helps identify and fix issues in LINQ queries.

public class QueryDebugger
{
    // SQL query logging
    public static IQueryable<T> LogQuery<T>(IQueryable<T> query, string operation = null)
    {
        var sql = query.ToQueryString();
        Console.WriteLine($"SQL Query ({operation}): {sql}");
        return query;
    }

    // Query execution tracing
    public static async Task<IEnumerable<T>> TraceQueryExecutionAsync<T>(
        IQueryable<T> query,
        Action<string> logger = null)
    {
        logger ??= Console.WriteLine;

        logger("Starting query execution...");
        var stopwatch = Stopwatch.StartNew();

        try
        {
            var result = await query.ToListAsync();
            stopwatch.Stop();

            logger($"Query completed in {stopwatch.ElapsedMilliseconds}ms");
            logger($"Result count: {result.Count}");

            return result;
        }
        catch (Exception ex)
        {
            stopwatch.Stop();
            logger($"Query failed after {stopwatch.ElapsedMilliseconds}ms");
            logger($"Error: {ex.Message}");
            throw;
        }
    }

    // Query validation
    public static ValidationResult ValidateQuery<T>(IQueryable<T> query)
    {
        var result = new ValidationResult();

        try
        {
            var sql = query.ToQueryString();

            // Check for potential issues
            if (sql.Contains("CROSS JOIN"))
            {
                result.Warnings.Add("Query contains CROSS JOIN which may cause performance issues");
            }

            if (sql.Split("SELECT").Length > 2)
            {
                result.Warnings.Add("Query may contain N+1 problem");
            }

            if (sql.Length > 1000)
            {
                result.Warnings.Add("Query is very long, consider optimization");
            }

            result.IsValid = true;
        }
        catch (Exception ex)
        {
            result.Errors.Add($"Query validation failed: {ex.Message}");
            result.IsValid = false;
        }

        return result;
    }

    // Query comparison
    public static QueryComparisonResult CompareQueries<T>(
        IQueryable<T> query1,
        IQueryable<T> query2)
    {
        var sql1 = query1.ToQueryString();
        var sql2 = query2.ToQueryString();

        return new QueryComparisonResult
        {
            Query1Sql = sql1,
            Query2Sql = sql2,
            AreEquivalent = sql1.Equals(sql2, StringComparison.OrdinalIgnoreCase),
            Differences = FindDifferences(sql1, sql2)
        };
    }

    private static List<string> FindDifferences(string sql1, string sql2)
    {
        var differences = new List<string>();

        if (sql1.Length != sql2.Length)
        {
            differences.Add($"Query lengths differ: {sql1.Length} vs {sql2.Length}");
        }

        // Add more detailed comparison logic here
        return differences;
    }
}

public class ValidationResult
{
    public bool IsValid { get; set; }
    public List<string> Errors { get; set; } = new List<string>();
    public List<string> Warnings { get; set; } = new List<string>();
}

public class QueryComparisonResult
{
    public string Query1Sql { get; set; }
    public string Query2Sql { get; set; }
    public bool AreEquivalent { get; set; }
    public List<string> Differences { get; set; } = new List<string>();
}

// Usage with debugging
public class DebuggableUserService
{
    public async Task<IEnumerable<User>> GetUsersWithDebuggingAsync()
    {
        var query = context.Users
            .Where(u => u.IsActive)
            .Include(u => u.Orders);

        // Log the generated SQL
        QueryDebugger.LogQuery(query, "GetActiveUsers");

        // Validate the query
        var validation = QueryDebugger.ValidateQuery(query);
        if (!validation.IsValid)
        {
            throw new InvalidOperationException($"Query validation failed: {string.Join(", ", validation.Errors)}");
        }

        // Trace execution
        return await QueryDebugger.TraceQueryExecutionAsync(query);
    }
}

39. How do you implement LINQ query profiling?

Answer: Query profiling provides detailed performance metrics and analysis.

public class QueryProfiler
{
    private readonly List<QueryProfile> _profiles = new List<QueryProfile>();
    private readonly object _lock = new object();

    // Profile a single query
    public async Task<QueryProfile> ProfileQueryAsync<T>(
        IQueryable<T> query,
        string queryName = null)
    {
        var profile = new QueryProfile
        {
            QueryName = queryName ?? typeof(T).Name,
            Timestamp = DateTime.UtcNow,
            QueryType = typeof(T).Name
        };

        var stopwatch = Stopwatch.StartNew();

        // Measure database round trip
        var dbStopwatch = Stopwatch.StartNew();
        var result = await query.ToListAsync();
        dbStopwatch.Stop();

        stopwatch.Stop();

        profile.DatabaseTime = dbStopwatch.Elapsed;
        profile.TotalTime = stopwatch.Elapsed;
        profile.ResultCount = result.Count;
        profile.SqlQuery = query.ToQueryString();
        profile.MemoryUsage = GC.GetTotalMemory(false);

        lock (_lock)
        {
            _profiles.Add(profile);
        }

        return profile;
    }

    // Get profiling statistics
    public QueryProfilingStats GetStats()
    {
        lock (_lock)
        {
            if (!_profiles.Any())
                return new QueryProfilingStats();

            return new QueryProfilingStats
            {
                TotalQueries = _profiles.Count,
                AverageExecutionTime = TimeSpan.FromTicks((long)_profiles.Average(p => p.TotalTime.Ticks)),
                AverageDatabaseTime = TimeSpan.FromTicks((long)_profiles.Average(p => p.DatabaseTime.Ticks)),
                SlowestQuery = _profiles.OrderByDescending(p => p.TotalTime).First(),
                FastestQuery = _profiles.OrderBy(p => p.TotalTime).First(),
                TotalResults = _profiles.Sum(p => p.ResultCount)
            };
        }
    }

    // Get slow queries
    public IEnumerable<QueryProfile> GetSlowQueries(TimeSpan threshold)
    {
        lock (_lock)
        {
            return _profiles.Where(p => p.TotalTime > threshold).OrderByDescending(p => p.TotalTime);
        }
    }

    // Export profiling data
    public string ExportProfilingData()
    {
        lock (_lock)
        {
            var json = JsonSerializer.Serialize(_profiles, new JsonSerializerOptions
            {
                WriteIndented = true
            });
            return json;
        }
    }

    // Clear profiling data
    public void ClearProfilingData()
    {
        lock (_lock)
        {
            _profiles.Clear();
        }
    }
}

public class QueryProfile
{
    public string QueryName { get; set; }
    public string QueryType { get; set; }
    public DateTime Timestamp { get; set; }
    public TimeSpan TotalTime { get; set; }
    public TimeSpan DatabaseTime { get; set; }
    public int ResultCount { get; set; }
    public string SqlQuery { get; set; }
    public long MemoryUsage { get; set; }
}

public class QueryProfilingStats
{
    public int TotalQueries { get; set; }
    public TimeSpan AverageExecutionTime { get; set; }
    public TimeSpan AverageDatabaseTime { get; set; }
    public QueryProfile SlowestQuery { get; set; }
    public QueryProfile FastestQuery { get; set; }
    public int TotalResults { get; set; }
}

// Usage with profiling
public class ProfiledUserService
{
    private readonly QueryProfiler _profiler = new QueryProfiler();

    public async Task<IEnumerable<User>> GetUsersWithProfilingAsync()
    {
        var query = context.Users
            .Where(u => u.IsActive)
            .Include(u => u.Orders);

        var profile = await _profiler.ProfileQueryAsync(query, "GetActiveUsersWithOrders");

        // Log slow queries
        if (profile.TotalTime > TimeSpan.FromSeconds(1))
        {
            Console.WriteLine($"Slow query detected: {profile.QueryName} took {profile.TotalTime}");
        }

        return await query.ToListAsync();
    }

    public void PrintProfilingStats()
    {
        var stats = _profiler.GetStats();
        Console.WriteLine($"Total queries: {stats.TotalQueries}");
        Console.WriteLine($"Average execution time: {stats.AverageExecutionTime}");
        Console.WriteLine($"Slowest query: {stats.SlowestQuery?.QueryName} ({stats.SlowestQuery?.TotalTime})");
    }
}

40. How do you handle LINQ query monitoring?

Answer: LINQ query monitoring involves tracking performance, execution plans, and resource usage of LINQ queries in production environments.

SQL Example:

-- Monitor slow queries
SELECT 
    qs.sql_handle,
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count as avg_elapsed_time,
    qs.total_logical_reads / qs.execution_count as avg_logical_reads,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) as query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qs.total_elapsed_time / qs.execution_count > 1000000 -- 1 second threshold
ORDER BY avg_elapsed_time DESC;

C# Implementation:

public class LinqQueryMonitor
{
    private readonly ILogger<LinqQueryMonitor> _logger;
    private readonly Stopwatch _stopwatch = new Stopwatch();

    public async Task<T> MonitorQuery<T>(Func<Task<T>> query, string queryName)
    {
        _stopwatch.Restart();
        try
        {
            var result = await query();
            _stopwatch.Stop();

            _logger.LogInformation("Query {QueryName} executed in {ElapsedMs}ms", 
                queryName, _stopwatch.ElapsedMilliseconds);

            return result;
        }
        catch (Exception ex)
        {
            _stopwatch.Stop();
            _logger.LogError(ex, "Query {QueryName} failed after {ElapsedMs}ms", 
                queryName, _stopwatch.ElapsedMilliseconds);
            throw;
        }
    }
}

// Usage
var monitor = new LinqQueryMonitor(logger);
var users = await monitor.MonitorQuery(
    () => context.Users.Where(u => u.IsActive).ToListAsync(),
    "GetActiveUsers"
);

41. How do you implement LINQ data transformation?

Answer: LINQ data transformation involves converting data from one format to another using projection, mapping, and conversion operators.

SQL Example:

-- Transform user data with calculated fields
SELECT 
    UserId,
    FirstName + ' ' + LastName AS FullName,
    CASE 
        WHEN Age < 18 THEN 'Minor'
        WHEN Age BETWEEN 18 AND 65 THEN 'Adult'
        ELSE 'Senior'
    END AS AgeCategory,
    CONVERT(VARCHAR(10), CreatedDate, 103) AS FormattedDate
FROM Users;

C# Implementation:

public class UserTransformer
{
    public IEnumerable<UserDto> TransformUsers(IEnumerable<User> users)
    {
        return users.Select(user => new UserDto
        {
            Id = user.Id,
            FullName = $"{user.FirstName} {user.LastName}",
            AgeCategory = GetAgeCategory(user.Age),
            FormattedCreatedDate = user.CreatedDate.ToString("dd/MM/yyyy"),
            EmailDomain = user.Email.Split('@').Last(),
            IsEligibleForDiscount = user.Age > 65 || user.TotalPurchases > 1000
        });
    }

    private string GetAgeCategory(int age) => age switch
    {
        < 18 => "Minor",
        >= 18 and <= 65 => "Adult",
        _ => "Senior"
    };
}

// Using AutoMapper for complex transformations
public class UserProfile : Profile
{
    public UserProfile()
    {
        CreateMap<User, UserDto>()
            .ForMember(dest => dest.FullName, 
                opt => opt.MapFrom(src => $"{src.FirstName} {src.LastName}"))
            .ForMember(dest => dest.AgeCategory, 
                opt => opt.MapFrom(src => GetAgeCategory(src.Age)))
            .ForMember(dest => dest.FormattedCreatedDate, 
                opt => opt.MapFrom(src => src.CreatedDate.ToString("dd/MM/yyyy")));
    }
}

42. How do you handle LINQ data mapping?

Answer: LINQ data mapping involves converting between different data models while maintaining data integrity and relationships.

SQL Example:

-- Map user data to different schemas
SELECT 
    u.UserId,
    u.FirstName,
    u.LastName,
    u.Email,
    p.PhoneNumber,
    a.AddressLine1,
    a.City,
    a.PostalCode
FROM Users u
LEFT JOIN UserProfiles p ON u.UserId = p.UserId
LEFT JOIN UserAddresses a ON u.UserId = a.UserId
WHERE u.IsActive = 1;

C# Implementation:

public class DataMapper
{
    public IEnumerable<UserViewModel> MapToViewModel(IEnumerable<User> users)
    {
        return users.Select(user => new UserViewModel
        {
            Id = user.Id,
            DisplayName = $"{user.FirstName} {user.LastName}",
            ContactInfo = new ContactInfo
            {
                Email = user.Email,
                Phone = user.Profile?.PhoneNumber,
                Address = user.Address != null ? new AddressViewModel
                {
                    Street = user.Address.AddressLine1,
                    City = user.Address.City,
                    PostalCode = user.Address.PostalCode
                } : null
            },
            Permissions = user.Roles.Select(r => r.Name).ToList()
        });
    }

    public User MapToEntity(UserCreateDto dto)
    {
        return new User
        {
            FirstName = dto.FirstName,
            LastName = dto.LastName,
            Email = dto.Email,
            CreatedDate = DateTime.UtcNow,
            Profile = new UserProfile
            {
                PhoneNumber = dto.PhoneNumber
            },
            Address = dto.Address != null ? new UserAddress
            {
                AddressLine1 = dto.Address.Street,
                City = dto.Address.City,
                PostalCode = dto.Address.PostalCode
            } : null
        };
    }
}

43. How do you implement LINQ data validation?

Answer: LINQ data validation ensures data integrity by applying business rules and constraints during query execution.

SQL Example:

-- Validate user data with constraints
SELECT 
    UserId,
    FirstName,
    LastName,
    Email,
    CASE 
        WHEN Email NOT LIKE '%_@__%.__%' THEN 'Invalid Email'
        WHEN LEN(FirstName) < 2 THEN 'First Name Too Short'
        WHEN LEN(LastName) < 2 THEN 'Last Name Too Short'
        WHEN Age < 0 OR Age > 150 THEN 'Invalid Age'
        ELSE 'Valid'
    END AS ValidationStatus
FROM Users
WHERE Email NOT LIKE '%_@__%.__%' 
   OR LEN(FirstName) < 2 
   OR LEN(LastName) < 2 
   OR Age < 0 OR Age > 150;

C# Implementation:

public class DataValidator
{
    public ValidationResult ValidateUser(User user)
    {
        var errors = new List<string>();

        if (string.IsNullOrWhiteSpace(user.FirstName) || user.FirstName.Length < 2)
            errors.Add("First name must be at least 2 characters");

        if (string.IsNullOrWhiteSpace(user.LastName) || user.LastName.Length < 2)
            errors.Add("Last name must be at least 2 characters");

        if (!IsValidEmail(user.Email))
            errors.Add("Invalid email format");

        if (user.Age < 0 || user.Age > 150)
            errors.Add("Age must be between 0 and 150");

        return new ValidationResult
        {
            IsValid = !errors.Any(),
            Errors = errors
        };
    }

    public IEnumerable<User> GetValidUsers(IEnumerable<User> users)
    {
        return users.Where(user => ValidateUser(user).IsValid);
    }

    public IEnumerable<ValidationError> GetValidationErrors(IEnumerable<User> users)
    {
        return users.SelectMany(user => 
            ValidateUser(user).Errors.Select(error => 
                new ValidationError { UserId = user.Id, Error = error }));
    }

    private bool IsValidEmail(string email)
    {
        try
        {
            var addr = new System.Net.Mail.MailAddress(email);
            return addr.Address == email;
        }
        catch
        {
            return false;
        }
    }
}

44. How do you handle LINQ data filtering?

Answer: LINQ data filtering involves applying conditions to select specific data based on criteria.

SQL Example:

-- Complex filtering with multiple conditions
SELECT 
    UserId,
    FirstName,
    LastName,
    Email,
    Age,
    CreatedDate
FROM Users
WHERE IsActive = 1
  AND Age BETWEEN 18 AND 65
  AND CreatedDate >= DATEADD(YEAR, -1, GETDATE())
  AND (Email LIKE '%@gmail.com' OR Email LIKE '%@yahoo.com')
  AND EXISTS (
      SELECT 1 FROM UserRoles ur 
      WHERE ur.UserId = Users.UserId 
        AND ur.RoleName IN ('Admin', 'Manager')
  )
ORDER BY CreatedDate DESC;

C# Implementation:

public class DataFilter
{
    public IEnumerable<User> FilterUsers(
        IEnumerable<User> users,
        UserFilterCriteria criteria)
    {
        return users.Where(user => 
            (!criteria.IsActive.HasValue || user.IsActive == criteria.IsActive) &&
            (!criteria.MinAge.HasValue || user.Age >= criteria.MinAge) &&
            (!criteria.MaxAge.HasValue || user.Age <= criteria.MaxAge) &&
            (!criteria.CreatedFrom.HasValue || user.CreatedDate >= criteria.CreatedFrom) &&
            (!criteria.CreatedTo.HasValue || user.CreatedDate <= criteria.CreatedTo) &&
            (string.IsNullOrEmpty(criteria.EmailDomain) || 
             user.Email.EndsWith($"@{criteria.EmailDomain}")) &&
            (criteria.Roles == null || !criteria.Roles.Any() || 
             user.Roles.Any(r => criteria.Roles.Contains(r.Name)))
        );
    }

    public IQueryable<User> BuildDynamicFilter(IQueryable<User> query, UserFilterCriteria criteria)
    {
        if (criteria.IsActive.HasValue)
            query = query.Where(u => u.IsActive == criteria.IsActive);

        if (criteria.MinAge.HasValue)
            query = query.Where(u => u.Age >= criteria.MinAge);

        if (criteria.MaxAge.HasValue)
            query = query.Where(u => u.Age <= criteria.MaxAge);

        if (!string.IsNullOrEmpty(criteria.SearchTerm))
            query = query.Where(u => 
                u.FirstName.Contains(criteria.SearchTerm) || 
                u.LastName.Contains(criteria.SearchTerm) || 
                u.Email.Contains(criteria.SearchTerm));

        return query;
    }
}

public class UserFilterCriteria
{
    public bool? IsActive { get; set; }
    public int? MinAge { get; set; }
    public int? MaxAge { get; set; }
    public DateTime? CreatedFrom { get; set; }
    public DateTime? CreatedTo { get; set; }
    public string EmailDomain { get; set; }
    public string SearchTerm { get; set; }
    public List<string> Roles { get; set; }
}

45. How do you implement LINQ data aggregation?

Answer: LINQ data aggregation involves summarizing data using functions like Sum, Count, Average, Min, Max, and custom aggregations.

SQL Example:

-- Complex aggregations with grouping
SELECT 
    DepartmentId,
    d.DepartmentName,
    COUNT(*) AS TotalEmployees,
    AVG(CAST(Salary AS FLOAT)) AS AverageSalary,
    MIN(Salary) AS MinSalary,
    MAX(Salary) AS MaxSalary,
    SUM(Salary) AS TotalSalary,
    COUNT(CASE WHEN IsActive = 1 THEN 1 END) AS ActiveEmployees,
    STRING_AGG(FirstName + ' ' + LastName, ', ') AS EmployeeNames
FROM Employees e
JOIN Departments d ON e.DepartmentId = d.DepartmentId
GROUP BY DepartmentId, d.DepartmentName
HAVING COUNT(*) > 5
ORDER BY AverageSalary DESC;

C# Implementation:

public class DataAggregator
{
    public IEnumerable<DepartmentSummary> AggregateByDepartment(IEnumerable<Employee> employees)
    {
        return employees
            .GroupBy(e => e.Department)
            .Select(g => new DepartmentSummary
            {
                DepartmentName = g.Key.Name,
                TotalEmployees = g.Count(),
                AverageSalary = g.Average(e => e.Salary),
                MinSalary = g.Min(e => e.Salary),
                MaxSalary = g.Max(e => e.Salary),
                TotalSalary = g.Sum(e => e.Salary),
                ActiveEmployees = g.Count(e => e.IsActive),
                EmployeeNames = string.Join(", ", g.Select(e => $"{e.FirstName} {e.LastName}")),
                SalaryDistribution = new SalaryDistribution
                {
                    LowRange = g.Count(e => e.Salary < 50000),
                    MidRange = g.Count(e => e.Salary >= 50000 && e.Salary < 100000),
                    HighRange = g.Count(e => e.Salary >= 100000)
                }
            })
            .Where(d => d.TotalEmployees > 5)
            .OrderByDescending(d => d.AverageSalary);
    }

    public SalesAnalytics AggregateSales(IEnumerable<Order> orders)
    {
        return new SalesAnalytics
        {
            TotalRevenue = orders.Sum(o => o.TotalAmount),
            TotalOrders = orders.Count(),
            AverageOrderValue = orders.Average(o => o.TotalAmount),
            TopProducts = orders
                .SelectMany(o => o.OrderItems)
                .GroupBy(oi => oi.Product)
                .Select(g => new ProductSales
                {
                    ProductName = g.Key.Name,
                    TotalSold = g.Sum(oi => oi.Quantity),
                    TotalRevenue = g.Sum(oi => oi.Quantity * oi.UnitPrice)
                })
                .OrderByDescending(p => p.TotalRevenue)
                .Take(10)
                .ToList(),
            MonthlyTrends = orders
                .GroupBy(o => new { o.OrderDate.Year, o.OrderDate.Month })
                .Select(g => new MonthlySales
                {
                    Year = g.Key.Year,
                    Month = g.Key.Month,
                    Revenue = g.Sum(o => o.TotalAmount),
                    OrderCount = g.Count()
                })
                .OrderBy(m => m.Year)
                .ThenBy(m => m.Month)
                .ToList()
        };
    }
}

46. How do you handle LINQ data grouping?

Answer: LINQ data grouping organizes data into logical collections based on common characteristics.

SQL Example:

-- Multi-level grouping with aggregations
SELECT 
    YEAR(OrderDate) AS OrderYear,
    MONTH(OrderDate) AS OrderMonth,
    CustomerId,
    c.CustomerName,
    COUNT(*) AS OrderCount,
    SUM(TotalAmount) AS TotalSpent,
    AVG(TotalAmount) AS AverageOrderValue
FROM Orders o
JOIN Customers c ON o.CustomerId = c.CustomerId
WHERE OrderDate >= DATEADD(YEAR, -2, GETDATE())
GROUP BY YEAR(OrderDate), MONTH(OrderDate), CustomerId, c.CustomerName
HAVING COUNT(*) > 1
ORDER BY OrderYear DESC, OrderMonth DESC, TotalSpent DESC;

C# Implementation:

public class DataGrouper
{
    public IEnumerable<CustomerOrderGroup> GroupOrdersByCustomer(IEnumerable<Order> orders)
    {
        return orders
            .GroupBy(o => o.Customer)
            .Select(g => new CustomerOrderGroup
            {
                Customer = g.Key,
                TotalOrders = g.Count(),
                TotalSpent = g.Sum(o => o.TotalAmount),
                AverageOrderValue = g.Average(o => o.TotalAmount),
                Orders = g.OrderByDescending(o => o.OrderDate).ToList(),
                OrderHistory = g
                    .GroupBy(o => new { o.OrderDate.Year, o.OrderDate.Month })
                    .Select(mg => new MonthlyOrderHistory
                    {
                        Year = mg.Key.Year,
                        Month = mg.Key.Month,
                        OrderCount = mg.Count(),
                        TotalSpent = mg.Sum(o => o.TotalAmount)
                    })
                    .OrderByDescending(h => h.Year)
                    .ThenByDescending(h => h.Month)
                    .ToList()
            })
            .Where(g => g.TotalOrders > 1)
            .OrderByDescending(g => g.TotalSpent);
    }

    public IEnumerable<ProductCategoryGroup> GroupProductsByCategory(IEnumerable<Product> products)
    {
        return products
            .GroupBy(p => p.Category)
            .Select(g => new ProductCategoryGroup
            {
                Category = g.Key,
                ProductCount = g.Count(),
                TotalValue = g.Sum(p => p.Price * p.StockQuantity),
                AveragePrice = g.Average(p => p.Price),
                Products = g.OrderBy(p => p.Name).ToList(),
                PriceRanges = g
                    .GroupBy(p => GetPriceRange(p.Price))
                    .Select(pr => new PriceRangeGroup
                    {
                        Range = pr.Key,
                        Count = pr.Count(),
                        Products = pr.ToList()
                    })
                    .OrderBy(pr => pr.Range)
                    .ToList()
            })
            .OrderBy(g => g.Category.Name);
    }

    private string GetPriceRange(decimal price) => price switch
    {
        < 10 => "Budget",
        >= 10 and < 50 => "Mid-Range",
        >= 50 and < 200 => "Premium",
        _ => "Luxury"
    };
}

47. How do you implement LINQ data sorting?

Answer: LINQ data sorting arranges data in ascending or descending order based on specified criteria.

SQL Example:

-- Complex sorting with multiple criteria
SELECT 
    EmployeeId,
    FirstName,
    LastName,
    DepartmentId,
    Salary,
    HireDate
FROM Employees
WHERE IsActive = 1
ORDER BY 
    DepartmentId ASC,
    Salary DESC,
    LastName ASC,
    FirstName ASC,
    HireDate DESC;

C# Implementation:

public class DataSorter
{
    public IEnumerable<Employee> SortEmployees(
        IEnumerable<Employee> employees,
        SortCriteria criteria)
    {
        var query = employees.AsQueryable();

        foreach (var sort in criteria.SortFields)
        {
            query = sort.Direction == SortDirection.Ascending
                ? query.OrderBy(GetSortExpression(sort.Field))
                : query.OrderByDescending(GetSortExpression(sort.Field));
        }

        return query;
    }

    public IEnumerable<Product> SortProducts(IEnumerable<Product> products)
    {
        return products
            .OrderBy(p => p.Category.Name)
            .ThenByDescending(p => p.Rating)
            .ThenBy(p => p.Name)
            .ThenByDescending(p => p.Price);
    }

    public IEnumerable<Order> SortOrdersWithCustomLogic(IEnumerable<Order> orders)
    {
        return orders
            .OrderBy(o => o.Status switch
            {
                OrderStatus.Pending => 1,
                OrderStatus.Processing => 2,
                OrderStatus.Shipped => 3,
                OrderStatus.Delivered => 4,
                OrderStatus.Cancelled => 5,
                _ => 6
            })
            .ThenByDescending(o => o.Priority)
            .ThenByDescending(o => o.OrderDate);
    }

    private Expression<Func<Employee, object>> GetSortExpression(string field) => field.ToLower() switch
    {
        "firstname" => e => e.FirstName,
        "lastname" => e => e.LastName,
        "department" => e => e.Department.Name,
        "salary" => e => e.Salary,
        "hiredate" => e => e.HireDate,
        _ => e => e.Id
    };
}

public class SortCriteria
{
    public List<SortField> SortFields { get; set; } = new();
}

public class SortField
{
    public string Field { get; set; }
    public SortDirection Direction { get; set; }
}

public enum SortDirection
{
    Ascending,
    Descending
}

48. How do you handle LINQ data projection?

Answer: LINQ data projection transforms data from one shape to another using the Select operator.

SQL Example:

-- Project user data to different shapes
SELECT 
    UserId,
    FirstName + ' ' + LastName AS FullName,
    Email,
    CASE 
        WHEN Age < 18 THEN 'Minor'
        WHEN Age BETWEEN 18 AND 65 THEN 'Adult'
        ELSE 'Senior'
    END AS AgeCategory,
    DATEDIFF(YEAR, CreatedDate, GETDATE()) AS YearsSinceRegistration
FROM Users
WHERE IsActive = 1;

C# Implementation:

public class DataProjector
{
    public IEnumerable<UserSummary> ProjectToUserSummary(IEnumerable<User> users)
    {
        return users.Select(user => new UserSummary
        {
            Id = user.Id,
            FullName = $"{user.FirstName} {user.LastName}",
            Email = user.Email,
            AgeCategory = GetAgeCategory(user.Age),
            YearsSinceRegistration = DateTime.Now.Year - user.CreatedDate.Year,
            IsActive = user.IsActive,
            LastLoginDate = user.LoginHistory?.OrderByDescending(l => l.LoginDate).FirstOrDefault()?.LoginDate
        });
    }

    public IEnumerable<OrderProjection> ProjectOrders(IEnumerable<Order> orders)
    {
        return orders.Select(order => new OrderProjection
        {
            OrderId = order.Id,
            CustomerName = $"{order.Customer.FirstName} {order.Customer.LastName}",
            OrderDate = order.OrderDate,
            TotalAmount = order.OrderItems.Sum(oi => oi.Quantity * oi.UnitPrice),
            ItemCount = order.OrderItems.Count(),
            Status = order.Status.ToString(),
            EstimatedDelivery = order.OrderDate.AddDays(GetDeliveryDays(order.Status)),
            Products = order.OrderItems.Select(oi => new ProductInfo
            {
                Name = oi.Product.Name,
                Quantity = oi.Quantity,
                UnitPrice = oi.UnitPrice,
                TotalPrice = oi.Quantity * oi.UnitPrice
            }).ToList()
        });
    }

    public IEnumerable<dynamic> ProjectToDynamic(IEnumerable<User> users)
    {
        return users.Select(user => new
        {
            user.Id,
            FullName = $"{user.FirstName} {user.LastName}",
            user.Email,
            AgeCategory = GetAgeCategory(user.Age),
            RegistrationYear = user.CreatedDate.Year,
            IsRecentUser = user.CreatedDate > DateTime.Now.AddYears(-1)
        });
    }

    private string GetAgeCategory(int age) => age switch
    {
        < 18 => "Minor",
        >= 18 and <= 65 => "Adult",
        _ => "Senior"
    };

    private int GetDeliveryDays(OrderStatus status) => status switch
    {
        OrderStatus.Pending => 7,
        OrderStatus.Processing => 5,
        OrderStatus.Shipped => 3,
        _ => 0
    };
}

49. How do you implement LINQ data conversion?

Answer: LINQ data conversion transforms data types and formats using casting, parsing, and conversion operators.

SQL Example:

-- Data type conversions
SELECT 
    UserId,
    CAST(Age AS VARCHAR(3)) AS AgeString,
    CONVERT(VARCHAR(10), CreatedDate, 103) AS FormattedDate,
    CONVERT(DECIMAL(10,2), Salary) AS SalaryDecimal,
    ISNULL(PhoneNumber, 'N/A') AS PhoneDisplay,
    CASE 
        WHEN IsActive = 1 THEN 'Yes'
        ELSE 'No'
    END AS ActiveStatus
FROM Users;

C# Implementation:

public class DataConverter
{
    public IEnumerable<UserDisplayModel> ConvertToDisplayModel(IEnumerable<User> users)
    {
        return users.Select(user => new UserDisplayModel
        {
            Id = user.Id.ToString(),
            FullName = $"{user.FirstName} {user.LastName}",
            Age = user.Age.ToString(),
            FormattedCreatedDate = user.CreatedDate.ToString("dd/MM/yyyy"),
            Salary = user.Salary.ToString("C"),
            PhoneNumber = user.PhoneNumber ?? "N/A",
            ActiveStatus = user.IsActive ? "Yes" : "No",
            EmailDomain = user.Email.Split('@').LastOrDefault() ?? "Unknown"
        });
    }

    public IEnumerable<OrderExportModel> ConvertForExport(IEnumerable<Order> orders)
    {
        return orders.Select(order => new OrderExportModel
        {
            OrderNumber = order.Id.ToString("D8"),
            CustomerId = order.CustomerId.ToString(),
            OrderDate = order.OrderDate.ToString("yyyy-MM-dd"),
            TotalAmount = order.TotalAmount.ToString("F2"),
            Status = order.Status.ToString(),
            Items = string.Join(";", order.OrderItems.Select(oi => 
                $"{oi.Product.Name}:{oi.Quantity}:{oi.UnitPrice:F2}")),
            FormattedTotal = $"${order.TotalAmount:F2}",
            IsHighValue = order.TotalAmount > 1000
        });
    }

    public IEnumerable<object> ConvertToGeneric(IEnumerable<User> users)
    {
        return users.Select(user => new
        {
            user.Id,
            DisplayName = $"{user.FirstName} {user.LastName}",
            Age = Convert.ToInt32(user.Age),
            CreatedYear = Convert.ToInt32(user.CreatedDate.Year),
            IsActive = Convert.ToBoolean(user.IsActive),
            Salary = Convert.ToDecimal(user.Salary)
        });
    }
}

50. How do you handle LINQ data formatting?

Answer: LINQ data formatting involves presenting data in specific formats for display, export, or reporting purposes.

SQL Example:

-- Format data for display
SELECT 
    UserId,
    UPPER(FirstName + ' ' + LastName) AS DisplayName,
    FORMAT(CreatedDate, 'dd/MM/yyyy') AS FormattedDate,
    FORMAT(Salary, 'C') AS FormattedSalary,
    CONCAT('+1-', SUBSTRING(PhoneNumber, 1, 3), '-', 
           SUBSTRING(PhoneNumber, 4, 3), '-', 
           SUBSTRING(PhoneNumber, 7, 4)) AS FormattedPhone,
    CASE 
        WHEN Email LIKE '%@gmail.com' THEN 'Gmail'
        WHEN Email LIKE '%@yahoo.com' THEN 'Yahoo'
        WHEN Email LIKE '%@outlook.com' THEN 'Outlook'
        ELSE 'Other'
    END AS EmailProvider
FROM Users;

C# Implementation:

public class DataFormatter
{
    public IEnumerable<FormattedUser> FormatUsers(IEnumerable<User> users)
    {
        return users.Select(user => new FormattedUser
        {
            Id = user.Id.ToString("D6"),
            DisplayName = $"{user.FirstName.ToUpper()} {user.LastName.ToUpper()}",
            FormattedCreatedDate = user.CreatedDate.ToString("dd/MM/yyyy"),
            FormattedSalary = user.Salary.ToString("C"),
            FormattedPhone = FormatPhoneNumber(user.PhoneNumber),
            EmailProvider = GetEmailProvider(user.Email),
            AgeGroup = GetAgeGroup(user.Age),
            StatusBadge = user.IsActive ? "🟢 Active" : "🔴 Inactive"
        });
    }

    public IEnumerable<ReportRow> FormatForReport(IEnumerable<Order> orders)
    {
        return orders.Select(order => new ReportRow
        {
            OrderNumber = $"ORD-{order.Id:D8}",
            CustomerName = $"{order.Customer.FirstName} {order.Customer.LastName}",
            OrderDate = order.OrderDate.ToString("MMM dd, yyyy"),
            TotalAmount = order.TotalAmount.ToString("C"),
            Status = order.Status.ToString().ToUpper(),
            ItemCount = $"{order.OrderItems.Count()} items",
            Priority = order.Priority switch
            {
                OrderPriority.Low => "🟡 Low",
                OrderPriority.Medium => "🟠 Medium",
                OrderPriority.High => "🔴 High",
                _ => "⚪ Normal"
            }
        });
    }

    private string FormatPhoneNumber(string phone)
    {
        if (string.IsNullOrEmpty(phone) || phone.Length != 10)
            return "N/A";

        return $"+1-{phone.Substring(0, 3)}-{phone.Substring(3, 3)}-{phone.Substring(6, 4)}";
    }

    private string GetEmailProvider(string email)
    {
        var domain = email.Split('@').LastOrDefault()?.ToLower();
        return domain switch
        {
            "gmail.com" => "Gmail",
            "yahoo.com" => "Yahoo",
            "outlook.com" => "Outlook",
            "hotmail.com" => "Hotmail",
            _ => "Other"
        };
    }

    private string GetAgeGroup(int age) => age switch
    {
        < 18 => "Teenager",
        >= 18 and < 30 => "Young Adult",
        >= 30 and < 50 => "Adult",
        >= 50 and < 65 => "Middle-aged",
        _ => "Senior"
    };
}

51. How do you implement LINQ error handling?

Answer: LINQ error handling involves managing exceptions that occur during query execution and providing fallback mechanisms.

SQL Example:

-- Error handling with TRY-CATCH
BEGIN TRY
    SELECT 
        UserId,
        FirstName,
        LastName,
        CAST(Age AS INT) AS AgeInt,
        CONVERT(DATE, CreatedDate) AS CreatedDateOnly
    FROM Users
    WHERE IsActive = 1;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage,
        ERROR_LINE() AS ErrorLine;
END CATCH

C# Implementation:

public class LinqErrorHandler
{
    private readonly ILogger<LinqErrorHandler> _logger;

    public LinqErrorHandler(ILogger<LinqErrorHandler> logger)
    {
        _logger = logger;
    }

    public async Task<IEnumerable<User>> ExecuteQueryWithErrorHandling(
        Func<Task<IEnumerable<User>>> query,
        string queryName)
    {
        try
        {
            return await query();
        }
        catch (InvalidOperationException ex)
        {
            _logger.LogError(ex, "Invalid operation in query {QueryName}", queryName);
            return Enumerable.Empty<User>();
        }
        catch (ArgumentNullException ex)
        {
            _logger.LogError(ex, "Null argument in query {QueryName}", queryName);
            throw;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Unexpected error in query {QueryName}", queryName);
            return Enumerable.Empty<User>();
        }
    }

    public IEnumerable<User> SafeQuery(IEnumerable<User> users, Func<User, bool> predicate)
    {
        try
        {
            return users.Where(predicate);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error in LINQ query execution");
            return Enumerable.Empty<User>();
        }
    }

    public IEnumerable<T> ExecuteWithFallback<T>(
        Func<IEnumerable<T>> primaryQuery,
        Func<IEnumerable<T>> fallbackQuery)
    {
        try
        {
            return primaryQuery();
        }
        catch (Exception ex)
        {
            _logger.LogWarning(ex, "Primary query failed, using fallback");
            return fallbackQuery();
        }
    }
}

52. How do you handle LINQ exception scenarios?

Answer: LINQ exception scenarios involve handling specific types of exceptions that can occur during query execution.

SQL Example:

-- Handle specific SQL exceptions
BEGIN TRY
    SELECT 
        UserId,
        CAST(Age AS INT) AS AgeInt,
        CONVERT(DECIMAL(10,2), Salary) AS SalaryDecimal
    FROM Users
    WHERE IsActive = 1;
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 245 -- Conversion failed
        SELECT 'Data conversion error occurred' AS ErrorMessage;
    ELSE IF ERROR_NUMBER() = 207 -- Invalid column name
        SELECT 'Column not found' AS ErrorMessage;
    ELSE
        SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH

C# Implementation:

public class LinqExceptionHandler
{
    private readonly ILogger<LinqExceptionHandler> _logger;

    public async Task<IEnumerable<User>> HandleQueryExceptions(
        Func<Task<IEnumerable<User>>> query)
    {
        try
        {
            return await query();
        }
        catch (InvalidOperationException ex) when (ex.Message.Contains("sequence"))
        {
            _logger.LogWarning("Empty sequence encountered: {Message}", ex.Message);
            return Enumerable.Empty<User>();
        }
        catch (ArgumentNullException ex)
        {
            _logger.LogError("Null argument provided: {Message}", ex.Message);
            throw;
        }
        catch (NotSupportedException ex)
        {
            _logger.LogError("Unsupported operation: {Message}", ex.Message);
            return Enumerable.Empty<User>();
        }
        catch (OverflowException ex)
        {
            _logger.LogError("Overflow in calculation: {Message}", ex.Message);
            return Enumerable.Empty<User>();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Unexpected error in LINQ query");
            throw;
        }
    }

    public IEnumerable<User> SafeAggregation(IEnumerable<User> users)
    {
        try
        {
            return users.Where(u => u.Age > 0)
                       .OrderBy(u => u.Age)
                       .Take(100);
        }
        catch (OverflowException)
        {
            _logger.LogWarning("Overflow in age calculation, using safe values");
            return users.Where(u => u.Age > 0 && u.Age < 150)
                       .OrderBy(u => u.Age)
                       .Take(100);
        }
    }
}

53. How do you implement LINQ retry logic?

Answer: LINQ retry logic involves automatically retrying failed queries with exponential backoff and circuit breaker patterns.

SQL Example:

-- Retry logic with WAITFOR
DECLARE @RetryCount INT = 0;
DECLARE @MaxRetries INT = 3;

WHILE @RetryCount < @MaxRetries
BEGIN
    BEGIN TRY
        SELECT UserId, FirstName, LastName FROM Users WHERE IsActive = 1;
        BREAK;
    END TRY
    BEGIN CATCH
        SET @RetryCount = @RetryCount + 1;
        IF @RetryCount < @MaxRetries
            WAITFOR DELAY '00:00:01'; -- Wait 1 second
        ELSE
            THROW;
    END CATCH
END

C# Implementation:

public class LinqRetryHandler
{
    private readonly ILogger<LinqRetryHandler> _logger;
    private readonly IAsyncPolicy<IEnumerable<User>> _retryPolicy;

    public LinqRetryHandler(ILogger<LinqRetryHandler> logger)
    {
        _logger = logger;
        _retryPolicy = Policy<IEnumerable<User>>
            .Handle<SqlException>()
            .Or<TimeoutException>()
            .WaitAndRetryAsync(
                retryCount: 3,
                sleepDurationProvider: retryAttempt => 
                    TimeSpan.FromSeconds(Math.Pow(2, retryAttempt)),
                onRetry: (exception, timeSpan, retryCount, context) =>
                {
                    _logger.LogWarning(
                        "Retry {RetryCount} after {Delay}ms due to {Error}",
                        retryCount, timeSpan.TotalMilliseconds, exception.Message);
                });
    }

    public async Task<IEnumerable<User>> ExecuteWithRetry(
        Func<Task<IEnumerable<User>>> query)
    {
        return await _retryPolicy.ExecuteAsync(async () =>
        {
            try
            {
                return await query();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Query execution failed");
                throw;
            }
        });
    }

    public async Task<IEnumerable<User>> ExecuteWithCircuitBreaker(
        Func<Task<IEnumerable<User>>> query)
    {
        var circuitBreaker = Policy<IEnumerable<User>>
            .Handle<Exception>()
            .CircuitBreakerAsync(
                exceptionsAllowedBeforeBreaking: 3,
                durationOfBreak: TimeSpan.FromMinutes(1),
                onBreak: (exception, duration) =>
                {
                    _logger.LogWarning("Circuit breaker opened for {Duration}", duration);
                },
                onReset: () =>
                {
                    _logger.LogInformation("Circuit breaker reset");
                });

        return await circuitBreaker.ExecuteAsync(query);
    }
}

54. How do you handle LINQ timeout scenarios?

Answer: LINQ timeout scenarios involve managing queries that take too long to execute and implementing timeout mechanisms.

SQL Example:

-- Set query timeout
SET LOCK_TIMEOUT 30000; -- 30 seconds
SET QUERY_TIMEOUT 60;   -- 60 seconds

SELECT UserId, FirstName, LastName 
FROM Users 
WHERE IsActive = 1
OPTION (MAXDOP 1, OPTIMIZE FOR UNKNOWN);

C# Implementation:

public class LinqTimeoutHandler
{
    private readonly ILogger<LinqTimeoutHandler> _logger;

    public async Task<IEnumerable<User>> ExecuteWithTimeout(
        Func<Task<IEnumerable<User>>> query,
        TimeSpan timeout)
    {
        using var cts = new CancellationTokenSource(timeout);

        try
        {
            return await query().WaitAsync(cts.Token);
        }
        catch (OperationCanceledException)
        {
            _logger.LogWarning("Query timed out after {Timeout}ms", timeout.TotalMilliseconds);
            return Enumerable.Empty<User>();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Query failed");
            throw;
        }
    }

    public async Task<IEnumerable<User>> ExecuteWithProgress(
        Func<Task<IEnumerable<User>>> query,
        IProgress<int> progress)
    {
        var users = new List<User>();
        var allUsers = await query();
        var total = allUsers.Count();
        var processed = 0;

        foreach (var user in allUsers)
        {
            users.Add(user);
            processed++;

            if (processed % 100 == 0)
            {
                progress.Report((int)((double)processed / total * 100));
            }
        }

        progress.Report(100);
        return users;
    }
}

55. How do you implement LINQ fallback strategies?

Answer: LINQ fallback strategies provide alternative data sources or query approaches when primary queries fail.

SQL Example:

-- Fallback strategy with COALESCE and ISNULL
SELECT 
    UserId,
    COALESCE(FirstName, 'Unknown') AS FirstName,
    ISNULL(LastName, 'Unknown') AS LastName,
    CASE 
        WHEN Email IS NOT NULL THEN Email
        WHEN PhoneNumber IS NOT NULL THEN PhoneNumber + '@temp.com'
        ELSE 'no-email@temp.com'
    END AS ContactInfo
FROM Users;

C# Implementation:

public class LinqFallbackHandler
{
    private readonly ILogger<LinqFallbackHandler> _logger;
    private readonly IUserRepository _primaryRepo;
    private readonly IUserRepository _fallbackRepo;

    public async Task<IEnumerable<User>> GetUsersWithFallback()
    {
        try
        {
            return await _primaryRepo.GetAllAsync();
        }
        catch (Exception ex)
        {
            _logger.LogWarning(ex, "Primary repository failed, using fallback");
            return await _fallbackRepo.GetAllAsync();
        }
    }

    public IEnumerable<User> GetUsersWithDataFallback(IEnumerable<User> users)
    {
        return users.Select(user => new User
        {
            Id = user.Id,
            FirstName = user.FirstName ?? "Unknown",
            LastName = user.LastName ?? "Unknown",
            Email = user.Email ?? $"{user.Id}@temp.com",
            Age = user.Age > 0 ? user.Age : 25,
            IsActive = user.IsActive
        });
    }

    public async Task<IEnumerable<User>> ExecuteWithMultipleFallbacks(
        Func<Task<IEnumerable<User>>> primaryQuery,
        params Func<Task<IEnumerable<User>>>[] fallbackQueries)
    {
        var exceptions = new List<Exception>();

        try
        {
            return await primaryQuery();
        }
        catch (Exception ex)
        {
            exceptions.Add(ex);
            _logger.LogWarning(ex, "Primary query failed");
        }

        foreach (var fallbackQuery in fallbackQueries)
        {
            try
            {
                return await fallbackQuery();
            }
            catch (Exception ex)
            {
                exceptions.Add(ex);
                _logger.LogWarning(ex, "Fallback query failed");
            }
        }

        _logger.LogError("All queries failed: {Exceptions}", 
            string.Join("; ", exceptions.Select(e => e.Message)));
        return Enumerable.Empty<User>();
    }
}

Error Handling & Monitoring (Questions 56-60)

56. How do you handle LINQ error recovery?

Answer: LINQ error recovery involves implementing resilient patterns that can handle failures gracefully and retry operations when appropriate.

Key Strategies: - Retry patterns with exponential backoff - Circuit breaker pattern - Fallback mechanisms - Graceful degradation

public class ResilientLinqService
{
    private readonly ILogger<ResilientLinqService> _logger;
    private readonly IRetryPolicy _retryPolicy;

    public ResilientLinqService(ILogger<ResilientLinqService> logger)
    {
        _logger = logger;
        _retryPolicy = Policy
            .Handle<SqlException>()
            .Or<TimeoutException>()
            .WaitAndRetryAsync(3, retryAttempt => 
                TimeSpan.FromSeconds(Math.Pow(2, retryAttempt)),
                onRetry: (exception, timeSpan, retryCount, context) =>
                {
                    _logger.LogWarning($"Retry {retryCount} after {timeSpan.TotalSeconds}s due to {exception.Message}");
                });
    }

    public async Task<IEnumerable<Customer>> GetCustomersWithRetryAsync()
    {
        return await _retryPolicy.ExecuteAsync(async () =>
        {
            try
            {
                using var context = new CustomerDbContext();
                return await context.Customers
                    .Where(c => c.IsActive)
                    .OrderBy(c => c.Name)
                    .ToListAsync();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Failed to retrieve customers");
                throw;
            }
        });
    }

    public async Task<IEnumerable<Customer>> GetCustomersWithFallbackAsync()
    {
        try
        {
            return await GetCustomersWithRetryAsync();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Primary data source failed, using fallback");
            return await GetCustomersFromCacheAsync();
        }
    }
}

57. How do you implement LINQ error logging?

Answer: Implement structured logging with correlation IDs, context information, and appropriate log levels.

public class LinqErrorLoggingService
{
    private readonly ILogger<LinqErrorLoggingService> _logger;
    private readonly ICorrelationIdProvider _correlationIdProvider;

    public LinqErrorLoggingService(ILogger<LinqErrorLoggingService> logger, 
                                 ICorrelationIdProvider correlationIdProvider)
    {
        _logger = logger;
        _correlationIdProvider = correlationIdProvider;
    }

    public async Task<IEnumerable<Order>> GetOrdersWithLoggingAsync(int customerId)
    {
        var correlationId = _correlationIdProvider.GetCorrelationId();
        var context = new Dictionary<string, object>
        {
            ["CustomerId"] = customerId,
            ["CorrelationId"] = correlationId,
            ["Operation"] = "GetOrders"
        };

        using (_logger.BeginScope(context))
        {
            try
            {
                _logger.LogInformation("Starting order retrieval for customer {CustomerId}", customerId);

                using var dbContext = new OrderDbContext();
                var query = dbContext.Orders
                    .Where(o => o.CustomerId == customerId)
                    .Include(o => o.OrderItems)
                    .OrderByDescending(o => o.OrderDate);

                // Log the generated SQL for debugging
                var sql = query.ToQueryString();
                _logger.LogDebug("Generated SQL: {Sql}", sql);

                var result = await query.ToListAsync();

                _logger.LogInformation("Successfully retrieved {Count} orders for customer {CustomerId}", 
                    result.Count, customerId);

                return result;
            }
            catch (SqlException ex)
            {
                _logger.LogError(ex, "Database error while retrieving orders for customer {CustomerId}. " +
                    "Error Code: {ErrorCode}, State: {State}", 
                    customerId, ex.Number, ex.State);
                throw;
            }
            catch (InvalidOperationException ex)
            {
                _logger.LogError(ex, "Invalid operation while retrieving orders for customer {CustomerId}. " +
                    "Query may be malformed or entity not found", customerId);
                throw;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Unexpected error while retrieving orders for customer {CustomerId}", 
                    customerId);
                throw;
            }
        }
    }
}

58. How do you handle LINQ error reporting?

Answer: Implement comprehensive error reporting with detailed context, metrics, and alerting capabilities.

public class LinqErrorReportingService
{
    private readonly ILogger<LinqErrorReportingService> _logger;
    private readonly IMetricsCollector _metrics;
    private readonly IErrorReporter _errorReporter;

    public LinqErrorReportingService(ILogger<LinqErrorReportingService> logger,
                                   IMetricsCollector metrics,
                                   IErrorReporter errorReporter)
    {
        _logger = logger;
        _metrics = metrics;
        _errorReporter = errorReporter;
    }

    public async Task<IEnumerable<Product>> GetProductsWithErrorReportingAsync()
    {
        var stopwatch = Stopwatch.StartNew();
        var operationId = Guid.NewGuid().ToString();

        try
        {
            using var context = new ProductDbContext();
            var result = await context.Products
                .Where(p => p.IsActive)
                .OrderBy(p => p.Name)
                .ToListAsync();

            stopwatch.Stop();
            _metrics.RecordSuccess("linq_query_duration", stopwatch.ElapsedMilliseconds);

            return result;
        }
        catch (Exception ex)
        {
            stopwatch.Stop();

            var errorContext = new ErrorContext
            {
                OperationId = operationId,
                Operation = "GetProducts",
                Query = "Products.Where(p => p.IsActive).OrderBy(p => p.Name)",
                Duration = stopwatch.ElapsedMilliseconds,
                Exception = ex,
                Timestamp = DateTime.UtcNow,
                Environment = Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT"),
                UserId = GetCurrentUserId(),
                AdditionalData = new Dictionary<string, object>
                {
                    ["DatabaseConnectionString"] = GetDatabaseConnectionInfo(),
                    ["EntityFrameworkVersion"] = typeof(DbContext).Assembly.GetName().Version.ToString()
                }
            };

            await ReportErrorAsync(errorContext);
            throw;
        }
    }

    private async Task ReportErrorAsync(ErrorContext errorContext)
    {
        // Log the error
        _logger.LogError(errorContext.Exception, 
            "LINQ query failed. Operation: {Operation}, Duration: {Duration}ms, OperationId: {OperationId}",
            errorContext.Operation, errorContext.Duration, errorContext.OperationId);

        // Record metrics
        _metrics.RecordError("linq_query_errors", 1);
        _metrics.RecordHistogram("linq_query_duration", errorContext.Duration);

        // Send to error reporting service
        await _errorReporter.ReportAsync(errorContext);

        // Alert if critical
        if (errorContext.Duration > 5000) // 5 seconds threshold
        {
            await SendPerformanceAlertAsync(errorContext);
        }
    }
}

public class ErrorContext
{
    public string OperationId { get; set; }
    public string Operation { get; set; }
    public string Query { get; set; }
    public long Duration { get; set; }
    public Exception Exception { get; set; }
    public DateTime Timestamp { get; set; }
    public string Environment { get; set; }
    public string UserId { get; set; }
    public Dictionary<string, object> AdditionalData { get; set; }
}

59. How do you implement LINQ error monitoring?

Answer: Implement comprehensive monitoring with metrics, health checks, and performance tracking.

public class LinqMonitoringService
{
    private readonly IMetricsCollector _metrics;
    private readonly IHealthCheckService _healthCheck;
    private readonly IPerformanceMonitor _performanceMonitor;

    public LinqMonitoringService(IMetricsCollector metrics,
                               IHealthCheckService healthCheck,
                               IPerformanceMonitor performanceMonitor)
    {
        _metrics = metrics;
        _healthCheck = healthCheck;
        _performanceMonitor = performanceMonitor;
    }

    public async Task<IEnumerable<Employee>> GetEmployeesWithMonitoringAsync()
    {
        var operationName = "GetEmployees";
        var stopwatch = Stopwatch.StartNew();

        using var scope = _performanceMonitor.BeginScope(operationName);

        try
        {
            // Record query start
            _metrics.IncrementCounter("linq_queries_started", new Dictionary<string, string>
            {
                ["operation"] = operationName
            });

            using var context = new EmployeeDbContext();

            // Monitor database connection health
            await _healthCheck.CheckDatabaseHealthAsync();

            var query = context.Employees
                .Where(e => e.IsActive)
                .Include(e => e.Department)
                .OrderBy(e => e.LastName);

            // Record query complexity
            var queryComplexity = AnalyzeQueryComplexity(query);
            _metrics.RecordHistogram("linq_query_complexity", queryComplexity);

            var result = await query.ToListAsync();

            stopwatch.Stop();

            // Record success metrics
            _metrics.RecordSuccess("linq_query_duration", stopwatch.ElapsedMilliseconds);
            _metrics.RecordHistogram("linq_result_count", result.Count);
            _metrics.IncrementCounter("linq_queries_successful");

            scope.SetSuccess();
            return result;
        }
        catch (Exception ex)
        {
            stopwatch.Stop();

            // Record failure metrics
            _metrics.RecordError("linq_query_duration", stopwatch.ElapsedMilliseconds);
            _metrics.IncrementCounter("linq_queries_failed");

            scope.SetFailure(ex);
            throw;
        }
    }

    private int AnalyzeQueryComplexity(IQueryable<Employee> query)
    {
        // Simple complexity analysis based on query structure
        var complexity = 0;
        var queryString = query.ToString();

        if (queryString.Contains("JOIN")) complexity += 10;
        if (queryString.Contains("WHERE")) complexity += 5;
        if (queryString.Contains("ORDER BY")) complexity += 3;
        if (queryString.Contains("GROUP BY")) complexity += 8;

        return complexity;
    }
}

// Health check implementation
public class DatabaseHealthCheck : IHealthCheck
{
    public async Task<HealthCheckResult> CheckHealthAsync(HealthCheckContext context, 
        CancellationToken cancellationToken = default)
    {
        try
        {
            using var dbContext = new ApplicationDbContext();
            await dbContext.Database.CanConnectAsync(cancellationToken);

            return HealthCheckResult.Healthy("Database is accessible");
        }
        catch (Exception ex)
        {
            return HealthCheckResult.Unhealthy("Database is not accessible", ex);
        }
    }
}

60. How do you handle LINQ error alerting?

Answer: Implement intelligent alerting with thresholds, escalation, and context-aware notifications.

public class LinqAlertingService
{
    private readonly IAlertService _alertService;
    private readonly IMetricsCollector _metrics;
    private readonly IConfiguration _configuration;

    public LinqAlertingService(IAlertService alertService,
                             IMetricsCollector metrics,
                             IConfiguration configuration)
    {
        _alertService = alertService;
        _metrics = metrics;
        _configuration = configuration;
    }

    public async Task<IEnumerable<Invoice>> GetInvoicesWithAlertingAsync()
    {
        var alertContext = new AlertContext
        {
            Operation = "GetInvoices",
            StartTime = DateTime.UtcNow,
            Thresholds = GetAlertThresholds()
        };

        try
        {
            using var context = new InvoiceDbContext();
            var result = await context.Invoices
                .Where(i => i.Status == InvoiceStatus.Pending)
                .Include(i => i.Customer)
                .OrderByDescending(i => i.DueDate)
                .ToListAsync();

            await CheckPerformanceThresholdsAsync(alertContext, result.Count);
            return result;
        }
        catch (Exception ex)
        {
            await HandleErrorAlertingAsync(alertContext, ex);
            throw;
        }
    }

    private async Task CheckPerformanceThresholdsAsync(AlertContext context, int resultCount)
    {
        var duration = DateTime.UtcNow - context.StartTime;
        var thresholds = context.Thresholds;

        // Performance alerts
        if (duration.TotalMilliseconds > thresholds.SlowQueryThreshold)
        {
            await _alertService.SendAlertAsync(new Alert
            {
                Level = AlertLevel.Warning,
                Title = "Slow LINQ Query Detected",
                Message = $"Query '{context.Operation}' took {duration.TotalMilliseconds}ms",
                Category = "Performance",
                Context = context,
                Timestamp = DateTime.UtcNow
            });
        }

        // Result count alerts
        if (resultCount > thresholds.LargeResultSetThreshold)
        {
            await _alertService.SendAlertAsync(new Alert
            {
                Level = AlertLevel.Info,
                Title = "Large Result Set Detected",
                Message = $"Query '{context.Operation}' returned {resultCount} records",
                Category = "Performance",
                Context = context,
                Timestamp = DateTime.UtcNow
            });
        }
    }

    private async Task HandleErrorAlertingAsync(AlertContext context, Exception ex)
    {
        var alertLevel = DetermineAlertLevel(ex);
        var escalationRequired = alertLevel == AlertLevel.Critical;

        var alert = new Alert
        {
            Level = alertLevel,
            Title = $"LINQ Query Error: {context.Operation}",
            Message = ex.Message,
            Category = "Error",
            Context = context,
            Exception = ex,
            Timestamp = DateTime.UtcNow,
            RequiresEscalation = escalationRequired
        };

        await _alertService.SendAlertAsync(alert);

        if (escalationRequired)
        {
            await EscalateAlertAsync(alert);
        }
    }

    private AlertLevel DetermineAlertLevel(Exception ex)
    {
        return ex switch
        {
            SqlException sqlEx when sqlEx.Number == -2 => AlertLevel.Critical, // Timeout
            SqlException sqlEx when sqlEx.Number == 53 => AlertLevel.Critical, // Connection failed
            SqlException => AlertLevel.Warning,
            InvalidOperationException => AlertLevel.Warning,
            _ => AlertLevel.Error
        };
    }
}

public class AlertContext
{
    public string Operation { get; set; }
    public DateTime StartTime { get; set; }
    public AlertThresholds Thresholds { get; set; }
}

public class AlertThresholds
{
    public int SlowQueryThreshold { get; set; } = 1000; // ms
    public int LargeResultSetThreshold { get; set; } = 1000; // records
    public int ErrorRateThreshold { get; set; } = 5; // percentage
}

Testing & Validation (Questions 61-70)

61. How do you implement LINQ unit testing?

Answer: Use mocking frameworks, in-memory databases, and test data builders for comprehensive unit testing.

[TestFixture]
public class CustomerServiceTests
{
    private Mock<ICustomerRepository> _mockRepository;
    private CustomerService _service;
    private List<Customer> _testCustomers;

    [SetUp]
    public void Setup()
    {
        _mockRepository = new Mock<ICustomerRepository>();
        _service = new CustomerService(_mockRepository.Object);

        _testCustomers = new List<Customer>
        {
            new Customer { Id = 1, Name = "John Doe", IsActive = true, Email = "john@example.com" },
            new Customer { Id = 2, Name = "Jane Smith", IsActive = false, Email = "jane@example.com" },
            new Customer { Id = 3, Name = "Bob Johnson", IsActive = true, Email = "bob@example.com" }
        };
    }

    [Test]
    public async Task GetActiveCustomers_ShouldReturnOnlyActiveCustomers()
    {
        // Arrange
        _mockRepository.Setup(r => r.GetAllAsync())
            .ReturnsAsync(_testCustomers);

        // Act
        var result = await _service.GetActiveCustomersAsync();

        // Assert
        Assert.That(result, Is.Not.Null);
        Assert.That(result.Count(), Is.EqualTo(2));
        Assert.That(result.All(c => c.IsActive), Is.True);
    }

    [Test]
    public async Task GetCustomersByName_ShouldReturnMatchingCustomers()
    {
        // Arrange
        var searchTerm = "john";
        _mockRepository.Setup(r => r.GetAllAsync())
            .ReturnsAsync(_testCustomers);

        // Act
        var result = await _service.GetCustomersByNameAsync(searchTerm);

        // Assert
        Assert.That(result, Is.Not.Null);
        Assert.That(result.Count(), Is.EqualTo(1));
        Assert.That(result.First().Name.ToLower(), Does.Contain(searchTerm));
    }

    [Test]
    public async Task GetCustomersByEmail_ShouldHandleCaseInsensitiveSearch()
    {
        // Arrange
        var email = "JOHN@EXAMPLE.COM";
        _mockRepository.Setup(r => r.GetAllAsync())
            .ReturnsAsync(_testCustomers);

        // Act
        var result = await _service.GetCustomersByEmailAsync(email);

        // Assert
        Assert.That(result, Is.Not.Null);
        Assert.That(result.Count(), Is.EqualTo(1));
        Assert.That(result.First().Email, Is.EqualTo("john@example.com"));
    }
}

// Using in-memory database for integration-style unit tests
[TestFixture]
public class CustomerDbContextTests
{
    private DbContextOptions<CustomerDbContext> _options;
    private CustomerDbContext _context;

    [SetUp]
    public void Setup()
    {
        _options = new DbContextOptionsBuilder<CustomerDbContext>()
            .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
            .Options;

        _context = new CustomerDbContext(_options);
        SeedTestData();
    }

    [TearDown]
    public void Cleanup()
    {
        _context.Database.EnsureDeleted();
        _context.Dispose();
    }

    [Test]
    public async Task GetActiveCustomers_ShouldReturnCorrectResults()
    {
        // Act
        var result = await _context.Customers
            .Where(c => c.IsActive)
            .OrderBy(c => c.Name)
            .ToListAsync();

        // Assert
        Assert.That(result, Is.Not.Null);
        Assert.That(result.Count, Is.EqualTo(2));
        Assert.That(result[0].Name, Is.EqualTo("Bob Johnson"));
        Assert.That(result[1].Name, Is.EqualTo("John Doe"));
    }

    private void SeedTestData()
    {
        var customers = new List<Customer>
        {
            new Customer { Name = "John Doe", IsActive = true, Email = "john@example.com" },
            new Customer { Name = "Jane Smith", IsActive = false, Email = "jane@example.com" },
            new Customer { Name = "Bob Johnson", IsActive = true, Email = "bob@example.com" }
        };

        _context.Customers.AddRange(customers);
        _context.SaveChanges();
    }
}

62. How do you handle LINQ integration testing?

Answer: Use test containers, database snapshots, and proper test isolation for integration testing.

[TestFixture]
public class CustomerIntegrationTests
{
    private TestContainer _testContainer;
    private CustomerDbContext _context;
    private CustomerService _service;

    [OneTimeSetUp]
    public async Task SetupContainer()
    {
        _testContainer = new TestcontainersBuilder<MsSqlTestcontainer>()
            .WithDatabase(new MsSqlTestcontainerConfiguration
            {
                Password = "StrongPassword123!",
                Port = 1433
            })
            .Build();

        await _testContainer.StartAsync();
    }

    [OneTimeTearDown]
    public async Task CleanupContainer()
    {
        await _testContainer.DisposeAsync();
    }

    [SetUp]
    public async Task Setup()
    {
        var options = new DbContextOptionsBuilder<CustomerDbContext>()
            .UseSqlServer(_testContainer.ConnectionString)
            .Options;

        _context = new CustomerDbContext(options);
        await _context.Database.EnsureCreatedAsync();
        await SeedTestDataAsync();

        var repository = new CustomerRepository(_context);
        _service = new CustomerService(repository);
    }

    [TearDown]
    public async Task Cleanup()
    {
        await _context.Database.EnsureDeletedAsync();
        await _context.DisposeAsync();
    }

    [Test]
    public async Task CreateCustomer_ShouldPersistToDatabase()
    {
        // Arrange
        var newCustomer = new Customer
        {
            Name = "Integration Test Customer",
            Email = "integration@test.com",
            IsActive = true
        };

        // Act
        var result = await _service.CreateCustomerAsync(newCustomer);

        // Assert
        Assert.That(result.Id, Is.GreaterThan(0));

        var persistedCustomer = await _context.Customers
            .FirstOrDefaultAsync(c => c.Id == result.Id);

        Assert.That(persistedCustomer, Is.Not.Null);
        Assert.That(persistedCustomer.Name, Is.EqualTo(newCustomer.Name));
    }

    [Test]
    public async Task UpdateCustomer_ShouldModifyExistingRecord()
    {
        // Arrange
        var customer = await _context.Customers.FirstAsync();
        var originalName = customer.Name;
        customer.Name = "Updated Name";

        // Act
        await _service.UpdateCustomerAsync(customer);

        // Assert
        var updatedCustomer = await _context.Customers
            .FirstAsync(c => c.Id == customer.Id);

        Assert.That(updatedCustomer.Name, Is.EqualTo("Updated Name"));
        Assert.That(updatedCustomer.Name, Is.Not.EqualTo(originalName));
    }

    [Test]
    public async Task DeleteCustomer_ShouldRemoveFromDatabase()
    {
        // Arrange
        var customer = await _context.Customers.FirstAsync();
        var customerId = customer.Id;

        // Act
        await _service.DeleteCustomerAsync(customerId);

        // Assert
        var deletedCustomer = await _context.Customers
            .FirstOrDefaultAsync(c => c.Id == customerId);

        Assert.That(deletedCustomer, Is.Null);
    }

    private async Task SeedTestDataAsync()
    {
        var customers = new List<Customer>
        {
            new Customer { Name = "Test Customer 1", Email = "test1@example.com", IsActive = true },
            new Customer { Name = "Test Customer 2", Email = "test2@example.com", IsActive = false },
            new Customer { Name = "Test Customer 3", Email = "test3@example.com", IsActive = true }
        };

        _context.Customers.AddRange(customers);
        await _context.SaveChangesAsync();
    }
}

63. How do you implement LINQ test data setup?

Answer: Use test data builders, factories, and fluent APIs for creating comprehensive test data.

public class CustomerTestDataBuilder
{
    private Customer _customer;

    public CustomerTestDataBuilder()
    {
        _customer = new Customer
        {
            Name = "Default Customer",
            Email = "default@example.com",
            IsActive = true,
            CreatedDate = DateTime.UtcNow
        };
    }

    public CustomerTestDataBuilder WithName(string name)
    {
        _customer.Name = name;
        return this;
    }

    public CustomerTestDataBuilder WithEmail(string email)
    {
        _customer.Email = email;
        return this;
    }

    public CustomerTestDataBuilder WithActiveStatus(bool isActive)
    {
        _customer.IsActive = isActive;
        return this;
    }

    public CustomerTestDataBuilder WithCreatedDate(DateTime createdDate)
    {
        _customer.CreatedDate = createdDate;
        return this;
    }

    public Customer Build()
    {
        return _customer;
    }

    public List<Customer> BuildMany(int count)
    {
        return Enumerable.Range(1, count)
            .Select(i => new CustomerTestDataBuilder()
                .WithName($"Customer {i}")
                .WithEmail($"customer{i}@example.com")
                .WithActiveStatus(i % 2 == 0)
                .Build())
            .ToList();
    }
}

public class OrderTestDataBuilder
{
    private Order _order;

    public OrderTestDataBuilder()
    {
        _order = new Order
        {
            OrderDate = DateTime.UtcNow,
            Status = OrderStatus.Pending,
            TotalAmount = 100.00m
        };
    }

    public OrderTestDataBuilder WithCustomer(Customer customer)
    {
        _order.CustomerId = customer.Id;
        _order.Customer = customer;
        return this;
    }

    public OrderTestDataBuilder WithStatus(OrderStatus status)
    {
        _order.Status = status;
        return this;
    }

    public OrderTestDataBuilder WithTotalAmount(decimal amount)
    {
        _order.TotalAmount = amount;
        return this;
    }

    public OrderTestDataBuilder WithOrderDate(DateTime orderDate)
    {
        _order.OrderDate = orderDate;
        return this;
    }

    public Order Build()
    {
        return _order;
    }
}

[TestFixture]
public class CustomerServiceTestsWithBuilders
{
    private Mock<ICustomerRepository> _mockRepository;
    private CustomerService _service;

    [SetUp]
    public void Setup()
    {
        _mockRepository = new Mock<ICustomerRepository>();
        _service = new CustomerService(_mockRepository.Object);
    }

    [Test]
    public async Task GetHighValueCustomers_ShouldReturnCorrectResults()
    {
        // Arrange
        var customers = new CustomerTestDataBuilder()
            .BuildMany(5)
            .Select((c, i) => new CustomerTestDataBuilder()
                .WithName(c.Name)
                .WithEmail(c.Email)
                .WithActiveStatus(true)
                .Build())
            .ToList();

        var orders = customers.Select(c => new OrderTestDataBuilder()
            .WithCustomer(c)
            .WithTotalAmount(1000.00m)
            .WithStatus(OrderStatus.Completed)
            .Build())
            .ToList();

        _mockRepository.Setup(r => r.GetAllWithOrdersAsync())
            .ReturnsAsync(customers);

        // Act
        var result = await _service.GetHighValueCustomersAsync(500.00m);

        // Assert
        Assert.That(result, Is.Not.Null);
        Assert.That(result.Count(), Is.EqualTo(5));
    }

    [Test]
    public async Task GetCustomersByDateRange_ShouldFilterCorrectly()
    {
        // Arrange
        var startDate = DateTime.UtcNow.AddDays(-30);
        var endDate = DateTime.UtcNow;

        var customers = new List<Customer>
        {
            new CustomerTestDataBuilder()
                .WithName("Recent Customer")
                .WithCreatedDate(DateTime.UtcNow.AddDays(-15))
                .Build(),
            new CustomerTestDataBuilder()
                .WithName("Old Customer")
                .WithCreatedDate(DateTime.UtcNow.AddDays(-60))
                .Build()
        };

        _mockRepository.Setup(r => r.GetAllAsync())
            .ReturnsAsync(customers);

        // Act
        var result = await _service.GetCustomersByDateRangeAsync(startDate, endDate);

        // Assert
        Assert.That(result, Is.Not.Null);
        Assert.That(result.Count(), Is.EqualTo(1));
        Assert.That(result.First().Name, Is.EqualTo("Recent Customer"));
    }
}

64. How do you handle LINQ test data cleanup?

Answer: Implement proper cleanup strategies using database transactions, test containers, and cleanup utilities.

public class TestDataCleanupService
{
    private readonly CustomerDbContext _context;
    private readonly ILogger<TestDataCleanupService> _logger;

    public TestDataCleanupService(CustomerDbContext context, ILogger<TestDataCleanupService> logger)
    {
        _context = context;
        _logger = logger;
    }

    public async Task CleanupTestDataAsync()
    {
        using var transaction = await _context.Database.BeginTransactionAsync();

        try
        {
            // Clean up in reverse order of dependencies
            await CleanupOrdersAsync();
            await CleanupCustomersAsync();

            await transaction.CommitAsync();
            _logger.LogInformation("Test data cleanup completed successfully");
        }
        catch (Exception ex)
        {
            await transaction.RollbackAsync();
            _logger.LogError(ex, "Failed to cleanup test data");
            throw;
        }
    }

    private async Task CleanupOrdersAsync()
    {
        var testOrders = await _context.Orders
            .Where(o => o.Customer.Email.Contains("@test.com"))
            .ToListAsync();

        if (testOrders.Any())
        {
            _context.Orders.RemoveRange(testOrders);
            await _context.SaveChangesAsync();
            _logger.LogDebug("Cleaned up {Count} test orders", testOrders.Count);
        }
    }

    private async Task CleanupCustomersAsync()
    {
        var testCustomers = await _context.Customers
            .Where(c => c.Email.Contains("@test.com"))
            .ToListAsync();

        if (testCustomers.Any())
        {
            _context.Customers.RemoveRange(testCustomers);
            await _context.SaveChangesAsync();
            _logger.LogDebug("Cleaned up {Count} test customers", testCustomers.Count);
        }
    }
}

[TestFixture]
public class CustomerServiceTestsWithCleanup
{
    private CustomerDbContext _context;
    private CustomerService _service;
    private TestDataCleanupService _cleanupService;
    private List<int> _createdCustomerIds;

    [SetUp]
    public async Task Setup()
    {
        var options = new DbContextOptionsBuilder<CustomerDbContext>()
            .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
            .Options;

        _context = new CustomerDbContext(options);
        await _context.Database.EnsureCreatedAsync();

        _cleanupService = new TestDataCleanupService(_context, 
            new Mock<ILogger<TestDataCleanupService>>().Object);

        var repository = new CustomerRepository(_context);
        _service = new CustomerService(repository);

        _createdCustomerIds = new List<int>();
    }

    [TearDown]
    public async Task Cleanup()
    {
        await _cleanupService.CleanupTestDataAsync();
        await _context.DisposeAsync();
    }

    [Test]
    public async Task CreateCustomer_ShouldCreateAndCleanup()
    {
        // Arrange
        var customer = new CustomerTestDataBuilder()
            .WithEmail("test@test.com")
            .Build();

        // Act
        var result = await _service.CreateCustomerAsync(customer);
        _createdCustomerIds.Add(result.Id);

        // Assert
        Assert.That(result.Id, Is.GreaterThan(0));

        // Verify cleanup will work
        var createdCustomer = await _context.Customers
            .FirstOrDefaultAsync(c => c.Id == result.Id);
        Assert.That(createdCustomer, Is.Not.Null);
    }
}

// Using test containers with automatic cleanup
[TestFixture]
public class IntegrationTestsWithContainerCleanup
{
    private TestContainer _testContainer;
    private CustomerDbContext _context;

    [OneTimeSetUp]
    public async Task SetupContainer()
    {
        _testContainer = new TestcontainersBuilder<MsSqlTestcontainer>()
            .WithDatabase(new MsSqlTestcontainerConfiguration
            {
                Password = "StrongPassword123!",
                Port = 1433
            })
            .WithCleanUp(true) // Automatic cleanup
            .Build();

        await _testContainer.StartAsync();
    }

    [OneTimeTearDown]
    public async Task CleanupContainer()
    {
        await _testContainer.DisposeAsync();
    }

    [SetUp]
    public async Task Setup()
    {
        var options = new DbContextOptionsBuilder<CustomerDbContext>()
            .UseSqlServer(_testContainer.ConnectionString)
            .Options;

        _context = new CustomerDbContext(options);
        await _context.Database.EnsureCreatedAsync();
    }

    [TearDown]
    public async Task Cleanup()
    {
        // Reset database to clean state
        await _context.Database.EnsureDeletedAsync();
        await _context.Database.EnsureCreatedAsync();
        await _context.DisposeAsync();
    }
}

65. How do you implement LINQ test isolation?

Answer: Use database transactions, unique database names, and proper test context isolation.

[TestFixture]
public class IsolatedCustomerTests
{
    private string _databaseName;
    private CustomerDbContext _context;
    private CustomerService _service;

    [SetUp]
    public async Task Setup()
    {
        // Create unique database name for each test
        _databaseName = $"TestDb_{Guid.NewGuid():N}";

        var options = new DbContextOptionsBuilder<CustomerDbContext>()
            .UseInMemoryDatabase(databaseName: _databaseName)
            .Options;

        _context = new CustomerDbContext(options);
        await _context.Database.EnsureCreatedAsync();

        var repository = new CustomerRepository(_context);
        _service = new CustomerService(repository);
    }

    [TearDown]
    public async Task Cleanup()
    {
        await _context.Database.EnsureDeletedAsync();
        await _context.DisposeAsync();
    }

    [Test]
    public async Task Test1_ShouldNotAffectTest2()
    {
        // Arrange
        var customer = new CustomerTestDataBuilder()
            .WithEmail("test1@example.com")
            .Build();

        // Act
        await _service.CreateCustomerAsync(customer);

        // Assert
        var count = await _context.Customers.CountAsync();
        Assert.That(count, Is.EqualTo(1));
    }

    [Test]
    public async Task Test2_ShouldStartWithCleanDatabase()
    {
        // Act
        var count = await _context.Customers.CountAsync();

        // Assert
        Assert.That(count, Is.EqualTo(0));
    }
}

// Using database transactions for isolation
[TestFixture]
public class TransactionalCustomerTests
{
    private CustomerDbContext _context;
    private CustomerService _service;

    [SetUp]
    public async Task Setup()
    {
        var options = new DbContextOptionsBuilder<CustomerDbContext>()
            .UseInMemoryDatabase(databaseName: "TransactionalTestDb")
            .Options;

        _context = new CustomerDbContext(options);
        await _context.Database.EnsureCreatedAsync();

        var repository = new CustomerRepository(_context);
        _service = new CustomerService(repository);
    }

    [TearDown]
    public async Task Cleanup()
    {
        await _context.Database.EnsureDeletedAsync();
        await _context.DisposeAsync();
    }

    [Test]
    public async Task CreateCustomer_WithTransaction_ShouldRollbackOnFailure()
    {
        // Arrange
        using var transaction = await _context.Database.BeginTransactionAsync();

        var customer = new CustomerTestDataBuilder()
            .WithEmail("transaction@test.com")
            .Build();

        // Act
        var result = await _service.CreateCustomerAsync(customer);

        // Verify customer was created
        var createdCustomer = await _context.Customers
            .FirstOrDefaultAsync(c => c.Id == result.Id);
        Assert.That(createdCustomer, Is.Not.Null);

        // Rollback transaction
        await transaction.RollbackAsync();

        // Assert customer was rolled back
        var rolledBackCustomer = await _context.Customers
            .FirstOrDefaultAsync(c => c.Id == result.Id);
        Assert.That(rolledBackCustomer, Is.Null);
    }
}

// Using test fixtures for shared setup
[TestFixture]
public class SharedCustomerTests
{
    private static readonly object _lock = new object();
    private static int _testCounter = 0;

    private string _testId;
    private CustomerDbContext _context;
    private CustomerService _service;

    [OneTimeSetUp]
    public void SetupTestId()
    {
        lock (_lock)
        {
            _testId = $"Test_{++_testCounter}";
        }
    }

    [SetUp]
    public async Task Setup()
    {
        var options = new DbContextOptionsBuilder<CustomerDbContext>()
            .UseInMemoryDatabase(databaseName: $"SharedTestDb_{_testId}")
            .Options;

        _context = new CustomerDbContext(options);
        await _context.Database.EnsureCreatedAsync();

        var repository = new CustomerRepository(_context);
        _service = new CustomerService(repository);
    }

    [TearDown]
    public async Task Cleanup()
    {
        await _context.Database.EnsureDeletedAsync();
        await _context.DisposeAsync();
    }

    [Test]
    public async Task IsolatedTest1()
    {
        // This test runs in isolation
        var customer = await _service.CreateCustomerAsync(
            new CustomerTestDataBuilder().WithEmail("isolated1@test.com").Build());

        Assert.That(customer.Id, Is.GreaterThan(0));
    }

    [Test]
    public async Task IsolatedTest2()
    {
        // This test also runs in isolation
        var customer = await _service.CreateCustomerAsync(
            new CustomerTestDataBuilder().WithEmail("isolated2@test.com").Build());

        Assert.That(customer.Id, Is.GreaterThan(0));
    }
}

66. How do you handle LINQ test performance?

Answer: Implement performance testing, benchmarking, and performance assertions for LINQ queries.

[TestFixture]
public class LinqPerformanceTests
{
    private CustomerDbContext _context;
    private CustomerService _service;
    private List<Customer> _largeDataSet;

    [SetUp]
    public async Task Setup()
    {
        var options = new DbContextOptionsBuilder<CustomerDbContext>()
            .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
            .Options;

        _context = new CustomerDbContext(options);
        await _context.Database.EnsureCreatedAsync();

        var repository = new CustomerRepository(_context);
        _service = new CustomerService(repository);

        // Create large dataset for performance testing
        _largeDataSet = new CustomerTestDataBuilder()
            .BuildMany(10000);

        _context.Customers.AddRange(_largeDataSet);
        await _context.SaveChangesAsync();
    }

    [TearDown]
    public async Task Cleanup()
    {
        await _context.Database.EnsureDeletedAsync();
        await _context.DisposeAsync();
    }

    [Test]
    public async Task GetActiveCustomers_ShouldCompleteWithinTimeLimit()
    {
        // Arrange
        var stopwatch = Stopwatch.StartNew();
        var maxExecutionTime = TimeSpan.FromMilliseconds(100);

        // Act
        var result = await _service.GetActiveCustomersAsync();
        stopwatch.Stop();

        // Assert
        Assert.That(stopwatch.Elapsed, Is.LessThan(maxExecutionTime));
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public async Task ComplexQuery_ShouldNotExceedMemoryLimit()
    {
        // Arrange
        var initialMemory = GC.GetTotalMemory(true);
        var maxMemoryIncrease = 50 * 1024 * 1024; // 50MB

        // Act
        var result = await _context.Customers
            .Where(c => c.IsActive)
            .Select(c => new
            {
                c.Id,
                c.Name,
                c.Email,
                OrderCount = c.Orders.Count,
                TotalSpent = c.Orders.Sum(o => o.TotalAmount)
            })
            .OrderByDescending(c => c.TotalSpent)
            .Take(1000)
            .ToListAsync();

        var finalMemory = GC.GetTotalMemory(true);
        var memoryIncrease = finalMemory - initialMemory;

        // Assert
        Assert.That(memoryIncrease, Is.LessThan(maxMemoryIncrease));
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public async Task LinqQuery_ShouldHaveReasonableExecutionTime()
    {
        // Arrange
        var performanceThreshold = new PerformanceThreshold
        {
            MaxExecutionTime = TimeSpan.FromMilliseconds(50),
            MaxMemoryUsage = 10 * 1024 * 1024, // 10MB
            MaxResultCount = 1000
        };

        // Act
        var performanceResult = await MeasureQueryPerformanceAsync(async () =>
        {
            return await _context.Customers
                .Where(c => c.IsActive)
                .OrderBy(c => c.Name)
                .Take(1000)
                .ToListAsync();
        });

        // Assert
        Assert.That(performanceResult.ExecutionTime, Is.LessThan(performanceThreshold.MaxExecutionTime));
        Assert.That(performanceResult.MemoryUsage, Is.LessThan(performanceThreshold.MaxMemoryUsage));
        Assert.That(performanceResult.ResultCount, Is.LessThanOrEqualTo(performanceThreshold.MaxResultCount));
    }

    [Test]
    public async Task LinqQuery_ShouldScaleLinearly()
    {
        // Test with different dataset sizes
        var datasetSizes = new[] { 100, 1000, 10000 };
        var executionTimes = new List<TimeSpan>();

        foreach (var size in datasetSizes)
        {
            // Create dataset of specific size
            var testData = new CustomerTestDataBuilder().BuildMany(size);
            _context.Customers.RemoveRange(_context.Customers);
            _context.Customers.AddRange(testData);
            await _context.SaveChangesAsync();

            // Measure execution time
            var stopwatch = Stopwatch.StartNew();
            var result = await _context.Customers
                .Where(c => c.IsActive)
                .OrderBy(c => c.Name)
                .ToListAsync();
            stopwatch.Stop();

            executionTimes.Add(stopwatch.Elapsed);
        }

        // Assert that execution time increases reasonably
        var timeRatio = executionTimes[2].TotalMilliseconds / executionTimes[1].TotalMilliseconds;
        var dataRatio = (double)datasetSizes[2] / datasetSizes[1];

        // Execution time should not increase more than 2x the data increase
        Assert.That(timeRatio, Is.LessThan(dataRatio * 2));
    }

    private async Task<PerformanceResult> MeasureQueryPerformanceAsync<T>(Func<Task<T>> query)
    {
        var initialMemory = GC.GetTotalMemory(true);
        var stopwatch = Stopwatch.StartNew();

        var result = await query();

        stopwatch.Stop();
        var finalMemory = GC.GetTotalMemory(true);

        return new PerformanceResult
        {
            ExecutionTime = stopwatch.Elapsed,
            MemoryUsage = finalMemory - initialMemory,
            ResultCount = result is IEnumerable enumerable ? enumerable.Cast<object>().Count() : 1
        };
    }
}

public class PerformanceThreshold
{
    public TimeSpan MaxExecutionTime { get; set; }
    public long MaxMemoryUsage { get; set; }
    public int MaxResultCount { get; set; }
}

public class PerformanceResult
{
    public TimeSpan ExecutionTime { get; set; }
    public long MemoryUsage { get; set; }
    public int ResultCount { get; set; }
}

// Performance benchmarking
[TestFixture]
public class LinqBenchmarkTests
{
    [Test]
    public async Task BenchmarkCustomerQueries()
    {
        var benchmark = new BenchmarkRunner();

        var results = await benchmark.RunAsync(new[]
        {
            new BenchmarkTest("Simple Where", async () =>
            {
                using var context = CreateContext();
                return await context.Customers.Where(c => c.IsActive).ToListAsync();
            }),
            new BenchmarkTest("Complex Join", async () =>
            {
                using var context = CreateContext();
                return await context.Customers
                    .Join(context.Orders, c => c.Id, o => o.CustomerId, (c, o) => new { c, o })
                    .Where(x => x.c.IsActive && x.o.Status == OrderStatus.Completed)
                    .ToListAsync();
            }),
            new BenchmarkTest("Group By", async () =>
            {
                using var context = CreateContext();
                return await context.Orders
                    .GroupBy(o => o.CustomerId)
                    .Select(g => new { CustomerId = g.Key, TotalOrders = g.Count() })
                    .ToListAsync();
            })
        });

        foreach (var result in results)
        {
            Console.WriteLine($"Query: {result.Name}, Time: {result.ExecutionTime.TotalMilliseconds}ms");
            Assert.That(result.ExecutionTime.TotalMilliseconds, Is.LessThan(100));
        }
    }
}

LINQ Testing (Questions 67-70)

67. How do you implement LINQ test mocking?

Answer: LINQ test mocking involves creating mock implementations of data sources and LINQ providers to test LINQ queries in isolation.

Key Approaches: - Mock IQueryable/IEnumerable sources - Use in-memory collections for testing - Mock LINQ providers (EF Core, etc.) - Test query logic separately from data access

Coding Example:

// Using Moq for mocking
public class UserServiceTests
{
    [Fact]
    public void GetActiveUsers_ShouldReturnFilteredResults()
    {
        // Arrange
        var mockUsers = new List<User>
        {
            new User { Id = 1, Name = "John", IsActive = true },
            new User { Id = 2, Name = "Jane", IsActive = false },
            new User { Id = 3, Name = "Bob", IsActive = true }
        }.AsQueryable();

        var mockRepository = new Mock<IUserRepository>();
        mockRepository.Setup(r => r.GetAll()).Returns(mockUsers);

        var service = new UserService(mockRepository.Object);

        // Act
        var result = service.GetActiveUsers().ToList();

        // Assert
        Assert.Equal(2, result.Count);
        Assert.All(result, user => Assert.True(user.IsActive));
    }
}

// Using TestData for complex scenarios
public class ComplexQueryTests
{
    [Fact]
    public void GetUsersByDepartment_ShouldGroupCorrectly()
    {
        // Arrange
        var testData = new TestDataBuilder()
            .WithUsers(10, u => u.Department = "IT")
            .WithUsers(5, u => u.Department = "HR")
            .WithUsers(8, u => u.Department = "Finance")
            .Build();

        var mockContext = new Mock<IApplicationDbContext>();
        mockContext.Setup(c => c.Users).Returns(testData.Users.AsQueryable());

        var service = new UserService(mockContext.Object);

        // Act
        var result = service.GetUsersByDepartment().ToList();

        // Assert
        Assert.Equal(3, result.Count);
        Assert.Contains(result, g => g.Department == "IT" && g.Count == 10);
    }
}

68. How do you handle LINQ test validation?

Answer: LINQ test validation ensures that LINQ queries produce expected results and handle edge cases correctly.

Validation Strategies: - Input validation testing - Output validation testing - Edge case handling - Performance validation - Memory usage validation

Coding Example:

public class LinqValidationTests
{
    [Theory]
    [InlineData(null, 0)]
    [InlineData("", 0)]
    [InlineData("test", 1)]
    [InlineData("TEST", 1)]
    public void SearchUsers_ShouldValidateInputs(string searchTerm, int expectedCount)
    {
        // Arrange
        var users = new List<User>
        {
            new User { Name = "test", Email = "test@example.com" }
        }.AsQueryable();

        var service = new UserService(users);

        // Act
        var result = service.SearchUsers(searchTerm);

        // Assert
        Assert.Equal(expectedCount, result.Count());
    }

    [Fact]
    public void ComplexQuery_ShouldValidatePerformance()
    {
        // Arrange
        var largeDataset = Enumerable.Range(1, 10000)
            .Select(i => new User { Id = i, Name = $"User{i}" })
            .AsQueryable();

        var service = new UserService(largeDataset);

        // Act & Assert
        var stopwatch = Stopwatch.StartNew();
        var result = service.GetUsersWithComplexFilter().ToList();
        stopwatch.Stop();

        Assert.True(stopwatch.ElapsedMilliseconds < 100, 
            "Query should complete within 100ms");
        Assert.True(result.Count > 0);
    }
}

69. How do you implement LINQ test assertions?

Answer: LINQ test assertions verify that LINQ queries produce the expected results, structure, and behavior.

Assertion Types: - Collection assertions - Single item assertions - Aggregate assertions - Order assertions - Performance assertions

Coding Example:

public class LinqAssertionTests
{
    [Fact]
    public void GetTopUsers_ShouldAssertCorrectOrdering()
    {
        // Arrange
        var users = new List<User>
        {
            new User { Id = 1, Score = 100 },
            new User { Id = 2, Score = 200 },
            new User { Id = 3, Score = 150 }
        }.AsQueryable();

        var service = new UserService(users);

        // Act
        var result = service.GetTopUsers(2).ToList();

        // Assert
        Assert.Equal(2, result.Count);
        Assert.Equal(200, result.First().Score); // Highest score first
        Assert.Equal(150, result.Last().Score);  // Second highest
        Assert.True(result.IsOrderedByDescending(u => u.Score));
    }

    [Fact]
    public void GroupUsers_ShouldAssertCorrectGrouping()
    {
        // Arrange
        var users = new List<User>
        {
            new User { Department = "IT", Salary = 50000 },
            new User { Department = "IT", Salary = 60000 },
            new User { Department = "HR", Salary = 45000 }
        }.AsQueryable();

        var service = new UserService(users);

        // Act
        var result = service.GetAverageSalaryByDepartment().ToList();

        // Assert
        Assert.Equal(2, result.Count);

        var itGroup = result.FirstOrDefault(g => g.Department == "IT");
        Assert.NotNull(itGroup);
        Assert.Equal(55000, itGroup.AverageSalary);

        var hrGroup = result.FirstOrDefault(g => g.Department == "HR");
        Assert.NotNull(hrGroup);
        Assert.Equal(45000, hrGroup.AverageSalary);
    }
}

70. How do you handle LINQ test coverage?

Answer: LINQ test coverage ensures comprehensive testing of LINQ queries, including all code paths, edge cases, and performance scenarios.

Coverage Strategies: - Unit test coverage for individual LINQ operations - Integration test coverage for complex queries - Performance test coverage - Memory usage coverage - Error handling coverage

Coding Example:

public class LinqCoverageTests
{
    [Fact]
    public void ComprehensiveQuery_ShouldHaveFullCoverage()
    {
        // Arrange - Test all possible scenarios
        var testScenarios = new[]
        {
            new { Users = new List<User>(), ExpectedCount = 0 },
            new { Users = new List<User> { new User { IsActive = true } }, ExpectedCount = 1 },
            new { Users = new List<User> { new User { IsActive = false } }, ExpectedCount = 0 },
            new { Users = new List<User> { 
                new User { IsActive = true, Department = "IT" },
                new User { IsActive = false, Department = "IT" },
                new User { IsActive = true, Department = "HR" }
            }, ExpectedCount = 2 }
        };

        foreach (var scenario in testScenarios)
        {
            // Act
            var service = new UserService(scenario.Users.AsQueryable());
            var result = service.GetActiveUsersByDepartment("IT").ToList();

            // Assert
            Assert.Equal(scenario.ExpectedCount, result.Count);
        }
    }

    [Fact]
    public void ErrorHandling_ShouldBeCovered()
    {
        // Arrange
        var nullUsers = (IQueryable<User>)null;
        var service = new UserService(nullUsers);

        // Act & Assert
        var exception = Assert.Throws<ArgumentNullException>(
            () => service.GetActiveUsers().ToList());

        Assert.Equal("users", exception.ParamName);
    }
}

Integration Patterns (Questions 71-80)

71. How do you implement LINQ with Entity Framework?

Answer: LINQ with Entity Framework provides a powerful way to query databases using strongly-typed queries that are translated to SQL.

Key Concepts: - IQueryable vs IEnumerable - Lazy loading vs eager loading - Query optimization - Change tracking

Coding Example:

public class UserRepository : IUserRepository
{
    private readonly ApplicationDbContext _context;

    public UserRepository(ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task<IEnumerable<User>> GetActiveUsersAsync()
    {
        return await _context.Users
            .Where(u => u.IsActive)
            .Include(u => u.Department)
            .Include(u => u.Roles)
            .OrderBy(u => u.LastName)
            .ThenBy(u => u.FirstName)
            .ToListAsync();
    }

    public async Task<IEnumerable<User>> GetUsersByDepartmentAsync(string department)
    {
        return await _context.Users
            .Where(u => u.Department.Name == department)
            .Select(u => new UserDto
            {
                Id = u.Id,
                FullName = $"{u.FirstName} {u.LastName}",
                Email = u.Email,
                DepartmentName = u.Department.Name
            })
            .ToListAsync();
    }

    public async Task<IEnumerable<DepartmentStats>> GetDepartmentStatisticsAsync()
    {
        return await _context.Users
            .GroupBy(u => u.Department)
            .Select(g => new DepartmentStats
            {
                DepartmentName = g.Key.Name,
                UserCount = g.Count(),
                AverageSalary = g.Average(u => u.Salary),
                MaxSalary = g.Max(u => u.Salary)
            })
            .ToListAsync();
    }
}

72. How do you handle LINQ with SQL Server?

Answer: LINQ with SQL Server involves optimizing queries for SQL Server-specific features and performance characteristics.

Optimization Techniques: - Query plan analysis - Index optimization - Stored procedure integration - SQL Server-specific features

Coding Example:

public class SqlServerOptimizedRepository
{
    private readonly ApplicationDbContext _context;

    public SqlServerOptimizedRepository(ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task<IEnumerable<User>> GetUsersWithOptimizedQueryAsync()
    {
        // Use SQL Server-specific optimizations
        return await _context.Users
            .FromSqlRaw(@"
                SELECT u.*, d.Name as DepartmentName 
                FROM Users u 
                INNER JOIN Departments d ON u.DepartmentId = d.Id 
                WHERE u.IsActive = 1 
                OPTION (RECOMPILE, OPTIMIZE FOR UNKNOWN)")
            .ToListAsync();
    }

    public async Task<IEnumerable<User>> GetUsersWithPaginationAsync(int page, int pageSize)
    {
        return await _context.Users
            .Where(u => u.IsActive)
            .OrderBy(u => u.Id)
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .ToListAsync();
    }

    public async Task<IEnumerable<User>> GetUsersWithFullTextSearchAsync(string searchTerm)
    {
        // Use SQL Server Full-Text Search
        return await _context.Users
            .FromSqlRaw(@"
                SELECT * FROM Users 
                WHERE CONTAINS((FirstName, LastName, Email), @searchTerm)",
                new SqlParameter("@searchTerm", searchTerm))
            .ToListAsync();
    }
}

73. How do you implement LINQ with NoSQL databases?

Answer: LINQ with NoSQL databases requires understanding the specific database's query capabilities and limitations.

Approaches: - Document database LINQ providers - Graph database queries - Key-value store operations - Column-family database queries

Coding Example:

// MongoDB with MongoDB.Driver
public class MongoUserRepository : IUserRepository
{
    private readonly IMongoCollection<User> _users;

    public MongoUserRepository(IMongoDatabase database)
    {
        _users = database.GetCollection<User>("users");
    }

    public async Task<IEnumerable<User>> GetActiveUsersAsync()
    {
        return await _users
            .Find(u => u.IsActive)
            .Sort(Builders<User>.Sort.Ascending(u => u.LastName))
            .ToListAsync();
    }

    public async Task<IEnumerable<User>> GetUsersByDepartmentAsync(string department)
    {
        var filter = Builders<User>.Filter.Eq(u => u.Department, department);
        var projection = Builders<User>.Projection
            .Include(u => u.Id)
            .Include(u => u.FirstName)
            .Include(u => u.LastName)
            .Include(u => u.Email);

        return await _users
            .Find(filter)
            .Project<User>(projection)
            .ToListAsync();
    }

    public async Task<IEnumerable<DepartmentStats>> GetDepartmentStatisticsAsync()
    {
        var pipeline = new[]
        {
            new BsonDocument("$group", new BsonDocument
            {
                { "_id", "$Department" },
                { "UserCount", new BsonDocument("$sum", 1) },
                { "AverageSalary", new BsonDocument("$avg", "$Salary") },
                { "MaxSalary", new BsonDocument("$max", "$Salary") }
            })
        };

        return await _users
            .Aggregate<DepartmentStats>(pipeline)
            .ToListAsync();
    }
}

74. How do you handle LINQ with REST APIs?

Answer: LINQ with REST APIs involves creating LINQ-like interfaces for HTTP-based data sources.

Implementation Patterns: - HTTP client wrappers - Query translation to HTTP requests - Response mapping - Caching strategies

Coding Example:

public class RestApiUserRepository : IUserRepository
{
    private readonly HttpClient _httpClient;
    private readonly IMemoryCache _cache;

    public RestApiUserRepository(HttpClient httpClient, IMemoryCache cache)
    {
        _httpClient = httpClient;
        _cache = cache;
    }

    public async Task<IEnumerable<User>> GetUsersAsync(UserQuery query)
    {
        var cacheKey = $"users_{query.GetHashCode()}";

        if (_cache.TryGetValue(cacheKey, out IEnumerable<User> cachedUsers))
        {
            return cachedUsers;
        }

        var queryParams = new List<string>();

        if (query.IsActive.HasValue)
            queryParams.Add($"isActive={query.IsActive}");

        if (!string.IsNullOrEmpty(query.Department))
            queryParams.Add($"department={Uri.EscapeDataString(query.Department)}");

        if (query.Page.HasValue)
            queryParams.Add($"page={query.Page}");

        if (query.PageSize.HasValue)
            queryParams.Add($"pageSize={query.PageSize}");

        var url = $"api/users?{string.Join("&", queryParams)}";
        var response = await _httpClient.GetAsync(url);

        if (response.IsSuccessStatusCode)
        {
            var users = await response.Content.ReadFromJsonAsync<IEnumerable<User>>();
            _cache.Set(cacheKey, users, TimeSpan.FromMinutes(5));
            return users;
        }

        throw new HttpRequestException($"Failed to fetch users: {response.StatusCode}");
    }

    public async Task<User> GetUserByIdAsync(int id)
    {
        var response = await _httpClient.GetAsync($"api/users/{id}");

        if (response.IsSuccessStatusCode)
        {
            return await response.Content.ReadFromJsonAsync<User>();
        }

        return null;
    }
}

// Query builder for REST API
public class UserQuery
{
    public bool? IsActive { get; set; }
    public string Department { get; set; }
    public int? Page { get; set; }
    public int? PageSize { get; set; }
}

75. How do you implement LINQ with message queues?

Answer: LINQ with message queues involves creating LINQ-like interfaces for processing messages from queues.

Patterns: - Message filtering - Message transformation - Batch processing - Error handling

Coding Example:

public class MessageQueueProcessor
{
    private readonly IMessageQueue _queue;
    private readonly ILogger<MessageQueueProcessor> _logger;

    public MessageQueueProcessor(IMessageQueue queue, ILogger<MessageQueueProcessor> logger)
    {
        _queue = queue;
        _logger = logger;
    }

    public async Task ProcessMessagesAsync()
    {
        var messages = await _queue.ReceiveMessagesAsync(10);

        var processedMessages = messages
            .Where(m => m.IsValid)
            .Select(m => new ProcessedMessage
            {
                Id = m.Id,
                Content = m.Content,
                ProcessedAt = DateTime.UtcNow
            })
            .ToList();

        foreach (var message in processedMessages)
        {
            await ProcessMessageAsync(message);
        }
    }

    public async Task<IEnumerable<Message>> GetMessagesByTypeAsync(string messageType)
    {
        var allMessages = await _queue.GetAllMessagesAsync();

        return allMessages
            .Where(m => m.Type == messageType)
            .OrderBy(m => m.Timestamp)
            .Take(100);
    }

    public async Task<IEnumerable<MessageStats>> GetMessageStatisticsAsync()
    {
        var messages = await _queue.GetAllMessagesAsync();

        return messages
            .GroupBy(m => m.Type)
            .Select(g => new MessageStats
            {
                MessageType = g.Key,
                Count = g.Count(),
                AverageProcessingTime = g.Average(m => m.ProcessingTime),
                ErrorCount = g.Count(m => m.HasError)
            });
    }
}

76. How do you handle LINQ with file systems?

Answer: LINQ with file systems involves creating LINQ-like interfaces for file and directory operations.

Use Cases: - File filtering and searching - Directory traversal - File content processing - Batch file operations

Coding Example:

public class FileSystemLinqProcessor
{
    public IEnumerable<FileInfo> GetFilesByExtension(string directory, string extension)
    {
        return new DirectoryInfo(directory)
            .GetFiles($"*.{extension}", SearchOption.AllDirectories)
            .Where(f => f.Length > 0)
            .OrderBy(f => f.Name);
    }

    public IEnumerable<FileInfo> GetLargeFiles(string directory, long minSizeInBytes)
    {
        return new DirectoryInfo(directory)
            .GetFiles("*", SearchOption.AllDirectories)
            .Where(f => f.Length > minSizeInBytes)
            .OrderByDescending(f => f.Length);
    }

    public IEnumerable<FileContent> ProcessTextFiles(string directory)
    {
        return new DirectoryInfo(directory)
            .GetFiles("*.txt", SearchOption.AllDirectories)
            .Select(f => new FileContent
            {
                FileName = f.Name,
                Content = File.ReadAllText(f.FullName),
                LineCount = File.ReadAllLines(f.FullName).Length,
                Size = f.Length
            })
            .Where(f => f.LineCount > 0);
    }

    public IEnumerable<DirectoryStats> GetDirectoryStatistics(string rootDirectory)
    {
        return new DirectoryInfo(rootDirectory)
            .GetDirectories("*", SearchOption.AllDirectories)
            .Select(d => new DirectoryStats
            {
                Name = d.Name,
                FileCount = d.GetFiles().Length,
                DirectoryCount = d.GetDirectories().Length,
                TotalSize = d.GetFiles().Sum(f => f.Length)
            })
            .OrderByDescending(d => d.TotalSize);
    }
}

public class FileContent
{
    public string FileName { get; set; }
    public string Content { get; set; }
    public int LineCount { get; set; }
    public long Size { get; set; }
}

public class DirectoryStats
{
    public string Name { get; set; }
    public int FileCount { get; set; }
    public int DirectoryCount { get; set; }
    public long TotalSize { get; set; }
}

77. How do you implement LINQ with XML data?

Answer: LINQ with XML data provides powerful querying capabilities for XML documents using LINQ to XML.

Features: - XDocument and XElement queries - XML transformation - XML validation - Namespace handling

Coding Example:

public class XmlLinqProcessor
{
    public IEnumerable<XElement> GetElementsByName(XDocument doc, string elementName)
    {
        return doc.Descendants()
            .Where(e => e.Name.LocalName == elementName)
            .OrderBy(e => e.Attribute("id")?.Value);
    }

    public IEnumerable<User> ParseUsersFromXml(string xmlContent)
    {
        var doc = XDocument.Parse(xmlContent);

        return doc.Descendants("User")
            .Select(u => new User
            {
                Id = int.Parse(u.Attribute("Id")?.Value ?? "0"),
                FirstName = u.Element("FirstName")?.Value,
                LastName = u.Element("LastName")?.Value,
                Email = u.Element("Email")?.Value,
                IsActive = bool.Parse(u.Element("IsActive")?.Value ?? "false")
            })
            .Where(u => u.IsActive);
    }

    public XDocument CreateUserXml(IEnumerable<User> users)
    {
        var doc = new XDocument(
            new XElement("Users",
                users.Select(u => new XElement("User",
                    new XAttribute("Id", u.Id),
                    new XElement("FirstName", u.FirstName),
                    new XElement("LastName", u.LastName),
                    new XElement("Email", u.Email),
                    new XElement("IsActive", u.IsActive)
                ))
            )
        );

        return doc;
    }

    public IEnumerable<DepartmentStats> GetDepartmentStatsFromXml(string xmlContent)
    {
        var doc = XDocument.Parse(xmlContent);

        return doc.Descendants("User")
            .GroupBy(u => u.Element("Department")?.Value)
            .Select(g => new DepartmentStats
            {
                DepartmentName = g.Key,
                UserCount = g.Count(),
                AverageSalary = g.Average(u => 
                    decimal.Parse(u.Element("Salary")?.Value ?? "0"))
            });
    }
}

78. How do you handle LINQ with JSON data?

Answer: LINQ with JSON data involves using System.Text.Json or Newtonsoft.Json to query and transform JSON data.

Approaches: - JsonDocument queries - JObject/JArray queries - JSON transformation - Dynamic JSON handling

Coding Example:

public class JsonLinqProcessor
{
    public IEnumerable<JsonElement> GetElementsByProperty(string jsonContent, string propertyName)
    {
        using var document = JsonDocument.Parse(jsonContent);

        return document.RootElement.EnumerateArray()
            .Where(element => element.TryGetProperty(propertyName, out _))
            .OrderBy(element => element.GetProperty("id").GetInt32());
    }

    public IEnumerable<User> ParseUsersFromJson(string jsonContent)
    {
        using var document = JsonDocument.Parse(jsonContent);

        return document.RootElement.GetProperty("users").EnumerateArray()
            .Select(user => new User
            {
                Id = user.GetProperty("id").GetInt32(),
                FirstName = user.GetProperty("firstName").GetString(),
                LastName = user.GetProperty("lastName").GetString(),
                Email = user.GetProperty("email").GetString(),
                IsActive = user.GetProperty("isActive").GetBoolean()
            })
            .Where(u => u.IsActive);
    }

    public string CreateUserJson(IEnumerable<User> users)
    {
        var userArray = users.Select(u => new
        {
            id = u.Id,
            firstName = u.FirstName,
            lastName = u.LastName,
            email = u.Email,
            isActive = u.IsActive
        });

        var result = new { users = userArray };
        return JsonSerializer.Serialize(result, new JsonSerializerOptions 
        { 
            WriteIndented = true 
        });
    }

    public IEnumerable<DepartmentStats> GetDepartmentStatsFromJson(string jsonContent)
    {
        using var document = JsonDocument.Parse(jsonContent);

        return document.RootElement.GetProperty("users").EnumerateArray()
            .GroupBy(user => user.GetProperty("department").GetString())
            .Select(g => new DepartmentStats
            {
                DepartmentName = g.Key,
                UserCount = g.Count(),
                AverageSalary = g.Average(user => 
                    user.GetProperty("salary").GetDecimal())
            });
    }
}

79. How do you implement LINQ with CSV data?

Answer: LINQ with CSV data involves parsing and querying CSV files using LINQ operations.

Techniques: - CSV parsing - Data transformation - Aggregation operations - Data validation

Coding Example:

public class CsvLinqProcessor
{
    public IEnumerable<CsvRow> ParseCsvFile(string filePath)
    {
        return File.ReadAllLines(filePath)
            .Skip(1) // Skip header
            .Where(line => !string.IsNullOrWhiteSpace(line))
            .Select(line => ParseCsvLine(line))
            .Where(row => row.IsValid);
    }

    public IEnumerable<User> ParseUsersFromCsv(string filePath)
    {
        return ParseCsvFile(filePath)
            .Select(row => new User
            {
                Id = int.Parse(row.GetValue("Id")),
                FirstName = row.GetValue("FirstName"),
                LastName = row.GetValue("LastName"),
                Email = row.GetValue("Email"),
                IsActive = bool.Parse(row.GetValue("IsActive"))
            })
            .Where(u => u.IsActive);
    }

    public IEnumerable<DepartmentStats> GetDepartmentStatsFromCsv(string filePath)
    {
        return ParseCsvFile(filePath)
            .GroupBy(row => row.GetValue("Department"))
            .Select(g => new DepartmentStats
            {
                DepartmentName = g.Key,
                UserCount = g.Count(),
                AverageSalary = g.Average(row => 
                    decimal.Parse(row.GetValue("Salary")))
            });
    }

    public void ExportToCsv<T>(IEnumerable<T> data, string filePath, string[] headers)
    {
        var csvLines = new List<string>
        {
            string.Join(",", headers)
        };

        csvLines.AddRange(data.Select(item => 
            string.Join(",", GetPropertyValues(item, headers))));

        File.WriteAllLines(filePath, csvLines);
    }

    private CsvRow ParseCsvLine(string line)
    {
        var values = line.Split(',');
        return new CsvRow(values);
    }

    private string[] GetPropertyValues<T>(T item, string[] propertyNames)
    {
        var type = typeof(T);
        return propertyNames.Select(name => 
            type.GetProperty(name)?.GetValue(item)?.ToString() ?? "")
            .ToArray();
    }
}

public class CsvRow
{
    private readonly string[] _values;

    public CsvRow(string[] values)
    {
        _values = values;
    }

    public string GetValue(string columnName)
    {
        // This is a simplified implementation
        // In practice, you'd map column names to indices
        return _values[0]; // Simplified
    }

    public bool IsValid => _values.Length > 0 && !_values.All(v => string.IsNullOrWhiteSpace(v));
}

80. How do you handle LINQ with binary data?

Answer: LINQ with binary data involves processing binary streams, files, and data structures using LINQ operations.

Use Cases: - Binary file processing - Stream analysis - Data pattern matching - Binary transformation

Coding Example:

public class BinaryLinqProcessor
{
    public IEnumerable<byte> ProcessBinaryFile(string filePath)
    {
        return File.ReadAllBytes(filePath)
            .Where(b => b > 0)
            .OrderBy(b => b);
    }

    public IEnumerable<BinaryChunk> FindPatterns(byte[] data, byte[] pattern)
    {
        return Enumerable.Range(0, data.Length - pattern.Length + 1)
            .Where(i => pattern.SequenceEqual(data.Skip(i).Take(pattern.Length)))
            .Select(i => new BinaryChunk
            {
                StartIndex = i,
                Data = data.Skip(i).Take(pattern.Length).ToArray()
            });
    }

    public IEnumerable<FileAnalysis> AnalyzeBinaryFiles(string directory)
    {
        return new DirectoryInfo(directory)
            .GetFiles("*", SearchOption.AllDirectories)
            .Where(f => f.Length > 0)
            .Select(f => new FileAnalysis
            {
                FileName = f.Name,
                Size = f.Length,
                ByteFrequency = AnalyzeByteFrequency(f.FullName),
                Entropy = CalculateEntropy(f.FullName)
            })
            .OrderByDescending(f => f.Entropy);
    }

    public IEnumerable<byte[]> SplitBinaryData(byte[] data, int chunkSize)
    {
        return Enumerable.Range(0, (data.Length + chunkSize - 1) / chunkSize)
            .Select(i => data.Skip(i * chunkSize).Take(chunkSize).ToArray());
    }

    private Dictionary<byte, int> AnalyzeByteFrequency(string filePath)
    {
        var bytes = File.ReadAllBytes(filePath);
        return bytes.GroupBy(b => b)
            .ToDictionary(g => g.Key, g => g.Count());
    }

    private double CalculateEntropy(string filePath)
    {
        var bytes = File.ReadAllBytes(filePath);
        var frequency = AnalyzeByteFrequency(filePath);
        var totalBytes = bytes.Length;

        return frequency.Values
            .Select(count => (double)count / totalBytes)
            .Where(p => p > 0)
            .Select(p => -p * Math.Log2(p))
            .Sum();
    }
}

public class BinaryChunk
{
    public int StartIndex { get; set; }
    public byte[] Data { get; set; }
}

public class FileAnalysis
{
    public string FileName { get; set; }
    public long Size { get; set; }
    public Dictionary<byte, int> ByteFrequency { get; set; }
    public double Entropy { get; set; }
}

Advanced Features (Questions 81-90)

81. How do you implement LINQ parallel operations?

Answer: LINQ parallel operations use PLINQ (Parallel LINQ) to execute queries across multiple CPU cores for improved performance.

Key Concepts: - AsParallel() extension - Parallel execution strategies - Thread safety considerations - Performance optimization

Coding Example:

public class ParallelLinqProcessor
{
    public IEnumerable<int> ProcessLargeDataSet(IEnumerable<int> data)
    {
        return data.AsParallel()
            .Where(x => x % 2 == 0)
            .Select(x => x * x)
            .OrderBy(x => x);
    }

    public IEnumerable<User> ProcessUsersInParallel(IEnumerable<User> users)
    {
        return users.AsParallel()
            .WithDegreeOfParallelism(Environment.ProcessorCount)
            .Where(u => u.IsActive)
            .Select(u => new UserDto
            {
                Id = u.Id,
                FullName = $"{u.FirstName} {u.LastName}",
                Email = u.Email
            })
            .OrderBy(u => u.FullName);
    }

    public async Task<IEnumerable<ProcessedData>> ProcessDataAsync(IEnumerable<RawData> data)
    {
        var tasks = data.AsParallel()
            .Select(async item => await ProcessItemAsync(item))
            .ToArray();

        return await Task.WhenAll(tasks);
    }

    public IEnumerable<AggregateResult> ParallelAggregation(IEnumerable<DataPoint> data)
    {
        return data.AsParallel()
            .GroupBy(d => d.Category)
            .Select(g => new AggregateResult
            {
                Category = g.Key,
                Count = g.Count(),
                Sum = g.Sum(d => d.Value),
                Average = g.Average(d => d.Value),
                Max = g.Max(d => d.Value),
                Min = g.Min(d => d.Value)
            });
    }

    private async Task<ProcessedData> ProcessItemAsync(RawData item)
    {
        // Simulate async processing
        await Task.Delay(100);
        return new ProcessedData
        {
            Id = item.Id,
            ProcessedValue = item.Value * 2,
            ProcessedAt = DateTime.UtcNow
        };
    }
}

82. How do you handle LINQ concurrent operations?

Answer: LINQ concurrent operations involve managing thread-safe operations when multiple threads access shared data.

Concurrency Patterns: - Thread-safe collections - Lock mechanisms - Concurrent dictionaries - Atomic operations

Coding Example:

public class ConcurrentLinqProcessor
{
    private readonly ConcurrentDictionary<int, User> _userCache;
    private readonly object _lockObject = new object();

    public ConcurrentLinqProcessor()
    {
        _userCache = new ConcurrentDictionary<int, User>();
    }

    public IEnumerable<User> ProcessUsersConcurrently(IEnumerable<User> users)
    {
        var results = new ConcurrentBag<User>();

        Parallel.ForEach(users, user =>
        {
            var processedUser = ProcessUser(user);
            results.Add(processedUser);
        });

        return results.OrderBy(u => u.Id);
    }

    public async Task<IEnumerable<User>> ProcessUsersWithSemaphoreAsync(
        IEnumerable<User> users, int maxConcurrency)
    {
        var semaphore = new SemaphoreSlim(maxConcurrency);
        var tasks = users.Select(async user =>
        {
            await semaphore.WaitAsync();
            try
            {
                return await ProcessUserAsync(user);
            }
            finally
            {
                semaphore.Release();
            }
        });

        return await Task.WhenAll(tasks);
    }

    public IEnumerable<User> GetUsersWithCache(IEnumerable<int> userIds)
    {
        return userIds.AsParallel()
            .Select(id => _userCache.GetOrAdd(id, LoadUserFromDatabase))
            .Where(u => u != null);
    }

    public IEnumerable<AggregateResult> ConcurrentAggregation(IEnumerable<DataPoint> data)
    {
        var results = new ConcurrentDictionary<string, AggregateResult>();

        data.AsParallel().ForAll(point =>
        {
            results.AddOrUpdate(
                point.Category,
                new AggregateResult { Category = point.Category },
                (key, existing) =>
                {
                    lock (existing)
                    {
                        existing.Count++;
                        existing.Sum += point.Value;
                        existing.Max = Math.Max(existing.Max, point.Value);
                        existing.Min = Math.Min(existing.Min, point.Value);
                        return existing;
                    }
                });
        });

        return results.Values;
    }

    private User ProcessUser(User user)
    {
        // Simulate processing
        Thread.Sleep(10);
        return new User
        {
            Id = user.Id,
            FirstName = user.FirstName.ToUpper(),
            LastName = user.LastName.ToUpper(),
            IsActive = user.IsActive
        };
    }

    private async Task<User> ProcessUserAsync(User user)
    {
        await Task.Delay(100);
        return ProcessUser(user);
    }

    private User LoadUserFromDatabase(int id)
    {
        // Simulate database load
        return new User { Id = id, FirstName = $"User{id}" };
    }
}

83. How do you implement LINQ streaming operations?

Answer: LINQ streaming operations process data as it becomes available, rather than loading everything into memory at once.

Streaming Patterns: - IEnumerable<T> streaming - IAsyncEnumerable<T> for async streaming - Memory-efficient processing - Real-time data processing

Coding Example:

public class StreamingLinqProcessor
{
    public IEnumerable<int> StreamNumbers(int count)
    {
        for (int i = 0; i < count; i++)
        {
            yield return i;
        }
    }

    public IEnumerable<User> StreamUsersFromDatabase()
    {
        using var connection = new SqlConnection(connectionString);
        connection.Open();

        using var command = new SqlCommand("SELECT * FROM Users", connection);
        using var reader = command.ExecuteReader();

        while (reader.Read())
        {
            yield return new User
            {
                Id = reader.GetInt32("Id"),
                FirstName = reader.GetString("FirstName"),
                LastName = reader.GetString("LastName"),
                Email = reader.GetString("Email")
            };
        }
    }

    public async IAsyncEnumerable<User> StreamUsersAsync()
    {
        using var connection = new SqlConnection(connectionString);
        await connection.OpenAsync();

        using var command = new SqlCommand("SELECT * FROM Users", connection);
        using var reader = await command.ExecuteReaderAsync();

        while (await reader.ReadAsync())
        {
            yield return new User
            {
                Id = reader.GetInt32("Id"),
                FirstName = reader.GetString("FirstName"),
                LastName = reader.GetString("LastName"),
                Email = reader.GetString("Email")
            };
        }
    }

    public IEnumerable<string> StreamFileLines(string filePath)
    {
        foreach (var line in File.ReadLines(filePath))
        {
            if (!string.IsNullOrWhiteSpace(line))
            {
                yield return line.Trim();
            }
        }
    }

    public IEnumerable<ProcessedData> StreamAndProcess(IEnumerable<RawData> dataStream)
    {
        return dataStream
            .Where(d => d.IsValid)
            .Select(d => new ProcessedData
            {
                Id = d.Id,
                ProcessedValue = d.Value * 2,
                ProcessedAt = DateTime.UtcNow
            })
            .Where(p => p.ProcessedValue > 0);
    }

    public async IAsyncEnumerable<Notification> StreamNotificationsAsync()
    {
        var queue = new Queue<Notification>();

        // Simulate real-time notifications
        while (true)
        {
            if (queue.Count > 0)
            {
                yield return queue.Dequeue();
            }
            else
            {
                await Task.Delay(100);
            }
        }
    }
}

84. How do you handle LINQ batch operations?

Answer: LINQ batch operations process data in chunks to optimize memory usage and performance.

Batch Processing Patterns: - Chunking data - Batch database operations - Memory management - Progress tracking

Coding Example:

public class BatchLinqProcessor
{
    public IEnumerable<IEnumerable<T>> Batch<T>(IEnumerable<T> source, int batchSize)
    {
        using var enumerator = source.GetEnumerator();
        while (enumerator.MoveNext())
        {
            yield return GetBatch(enumerator, batchSize);
        }
    }

    private IEnumerable<T> GetBatch<T>(IEnumerator<T> enumerator, int batchSize)
    {
        do
        {
            yield return enumerator.Current;
        }
        while (--batchSize > 0 && enumerator.MoveNext());
    }

    public async Task ProcessUsersInBatchesAsync(IEnumerable<User> users, int batchSize)
    {
        var batches = Batch(users, batchSize);

        foreach (var batch in batches)
        {
            await ProcessBatchAsync(batch.ToList());
        }
    }

    public async Task<IEnumerable<User>> LoadUsersInBatchesAsync(int totalCount, int batchSize)
    {
        var allUsers = new List<User>();

        for (int offset = 0; offset < totalCount; offset += batchSize)
        {
            var batch = await LoadUserBatchAsync(offset, batchSize);
            allUsers.AddRange(batch);
        }

        return allUsers;
    }

    public async Task<IEnumerable<ProcessedResult>> ProcessDataInBatchesAsync(
        IEnumerable<RawData> data, int batchSize, IProgress<int> progress = null)
    {
        var results = new List<ProcessedResult>();
        var batches = Batch(data, batchSize).ToList();
        var processedBatches = 0;

        foreach (var batch in batches)
        {
            var batchResults = await ProcessBatchAsync(batch.ToList());
            results.AddRange(batchResults);

            processedBatches++;
            progress?.Report((processedBatches * 100) / batches.Count);
        }

        return results;
    }

    public async Task BulkInsertUsersAsync(IEnumerable<User> users, int batchSize)
    {
        var batches = Batch(users, batchSize);

        foreach (var batch in batches)
        {
            await BulkInsertBatchAsync(batch.ToList());
        }
    }

    private async Task ProcessBatchAsync(List<User> batch)
    {
        // Simulate batch processing
        await Task.Delay(100 * batch.Count);

        foreach (var user in batch)
        {
            // Process individual user
            user.ProcessedAt = DateTime.UtcNow;
        }
    }

    private async Task<IEnumerable<User>> LoadUserBatchAsync(int offset, int batchSize)
    {
        // Simulate database batch load
        await Task.Delay(50);

        return Enumerable.Range(offset, batchSize)
            .Select(i => new User { Id = i, FirstName = $"User{i}" });
    }

    private async Task<IEnumerable<ProcessedResult>> ProcessBatchAsync(List<RawData> batch)
    {
        await Task.Delay(100);

        return batch.Select(d => new ProcessedResult
        {
            Id = d.Id,
            ProcessedValue = d.Value * 2
        });
    }

    private async Task BulkInsertBatchAsync(List<User> batch)
    {
        // Simulate bulk insert
        await Task.Delay(200);
    }
}

85. How do you implement LINQ reactive programming?

Answer: LINQ reactive programming uses Rx (Reactive Extensions) to handle asynchronous data streams and events.

Reactive Patterns: - Observable sequences - Event streams - Data flow programming - Asynchronous event handling

Coding Example:

public class ReactiveLinqProcessor
{
    public IObservable<User> CreateUserStream(IEnumerable<User> users)
    {
        return users.ToObservable()
            .Where(u => u.IsActive)
            .Select(u => new UserDto
            {
                Id = u.Id,
                FullName = $"{u.FirstName} {u.LastName}",
                Email = u.Email
            });
    }

    public IObservable<User> CreateUserStreamWithThrottling(IEnumerable<User> users)
    {
        return users.ToObservable()
            .Throttle(TimeSpan.FromMilliseconds(100))
            .Where(u => u.IsActive)
            .DistinctUntilChanged(u => u.Id);
    }

    public IObservable<AggregateResult> CreateAggregateStream(IEnumerable<DataPoint> dataPoints)
    {
        return dataPoints.ToObservable()
            .Buffer(TimeSpan.FromSeconds(5), 100) // Buffer by time or count
            .Where(buffer => buffer.Count > 0)
            .Select(buffer => new AggregateResult
            {
                Count = buffer.Count,
                Sum = buffer.Sum(d => d.Value),
                Average = buffer.Average(d => d.Value),
                Timestamp = DateTime.UtcNow
            });
    }

    public IObservable<User> CreateUserEventStream()
    {
        return Observable.Create<User>(observer =>
        {
            var timer = Observable.Interval(TimeSpan.FromSeconds(1))
                .Subscribe(_ =>
                {
                    var user = GenerateRandomUser();
                    observer.OnNext(user);
                });

            return timer;
        });
    }

    public IObservable<User> CreateUserStreamWithErrorHandling(IEnumerable<User> users)
    {
        return users.ToObservable()
            .Select(user =>
            {
                if (user == null)
                    throw new ArgumentNullException(nameof(user));
                return user;
            })
            .Catch<User, Exception>(ex =>
            {
                Console.WriteLine($"Error processing user: {ex.Message}");
                return Observable.Empty<User>();
            })
            .Retry(3);
    }

    public IObservable<User> CreateUserStreamWithBackpressure(IEnumerable<User> users)
    {
        return users.ToObservable()
            .Buffer(10) // Process in batches of 10
            .SelectMany(batch => batch)
            .Where(u => u.IsActive);
    }

    public IObservable<User> CreateUserStreamWithTimeout(IEnumerable<User> users)
    {
        return users.ToObservable()
            .Timeout(TimeSpan.FromSeconds(5))
            .Where(u => u.IsActive)
            .Catch<User, TimeoutException>(ex =>
            {
                Console.WriteLine("Operation timed out");
                return Observable.Empty<User>();
            });
    }

    private User GenerateRandomUser()
    {
        return new User
        {
            Id = Random.Shared.Next(1, 1000),
            FirstName = $"User{Random.Shared.Next(1, 100)}",
            LastName = $"Last{Random.Shared.Next(1, 100)}",
            IsActive = Random.Shared.Next(2) == 1
        };
    }
}

LINQ Programming Paradigms

86. How do you handle LINQ event-driven programming?

Answer: LINQ event-driven programming combines LINQ with reactive programming patterns to handle asynchronous data streams and events.

Key Concepts: - Use IObservable<T> and IObserver<T> interfaces - Leverage Reactive Extensions (Rx) for LINQ to Events - Handle event streams with LINQ operators - Implement event aggregation and filtering

Coding Example:

using System;
using System.Reactive.Linq;
using System.Reactive.Subjects;

public class EventDrivenLinqExample
{
    private Subject<OrderEvent> _orderEvents = new Subject<OrderEvent>();

    public void SetupEventDrivenLinq()
    {
        // Subscribe to high-value orders
        var highValueOrders = _orderEvents
            .Where(order => order.Amount > 1000)
            .Buffer(TimeSpan.FromMinutes(5)) // Group events over 5 minutes
            .Where(orders => orders.Count >= 3)
            .Subscribe(orders => ProcessHighValueOrderBatch(orders));

        // Monitor order frequency
        var orderFrequency = _orderEvents
            .GroupBy(order => order.CustomerId)
            .SelectMany(group => group
                .Buffer(TimeSpan.FromMinutes(10))
                .Select(orders => new { CustomerId = group.Key, OrderCount = orders.Count }))
            .Where(x => x.OrderCount > 5)
            .Subscribe(x => AlertHighFrequencyCustomer(x));
    }

    public void EmitOrderEvent(OrderEvent orderEvent)
    {
        _orderEvents.OnNext(orderEvent);
    }
}

public class OrderEvent
{
    public int CustomerId { get; set; }
    public decimal Amount { get; set; }
    public DateTime Timestamp { get; set; }
}

87. How do you implement LINQ functional programming?

Answer: LINQ functional programming emphasizes immutability, pure functions, and declarative data transformations using functional programming principles.

Key Principles: - Immutable data structures - Pure functions (no side effects) - Higher-order functions - Function composition - Declarative over imperative

Coding Example:

public class FunctionalLinqExample
{
    // Pure function - no side effects, same input always produces same output
    public static IEnumerable<OrderSummary> ProcessOrdersFunctionally(
        IEnumerable<Order> orders,
        Func<Order, bool> filter,
        Func<Order, OrderSummary> transform)
    {
        return orders
            .Where(filter)
            .Select(transform)
            .OrderBy(x => x.TotalAmount);
    }

    // Function composition
    public static Func<Order, bool> CreateOrderFilter(decimal minAmount, string category)
    {
        return order => order.Amount >= minAmount && order.Category == category;
    }

    public static Func<Order, OrderSummary> CreateOrderTransformer()
    {
        return order => new OrderSummary
        {
            OrderId = order.Id,
            TotalAmount = order.Amount,
            ProcessedDate = DateTime.UtcNow
        };
    }

    // Immutable data processing
    public static IEnumerable<CustomerReport> GenerateCustomerReports(
        IEnumerable<Order> orders)
    {
        return orders
            .GroupBy(o => o.CustomerId)
            .Select(group => new CustomerReport
            {
                CustomerId = group.Key,
                TotalOrders = group.Count(),
                TotalAmount = group.Sum(o => o.Amount),
                AverageOrderValue = group.Average(o => o.Amount),
                LastOrderDate = group.Max(o => o.OrderDate)
            })
            .OrderByDescending(r => r.TotalAmount);
    }
}

// Immutable data structures
public record OrderSummary(int OrderId, decimal TotalAmount, DateTime ProcessedDate);
public record CustomerReport(int CustomerId, int TotalOrders, decimal TotalAmount, 
    decimal AverageOrderValue, DateTime LastOrderDate);

88. How do you handle LINQ declarative programming?

Answer: LINQ declarative programming focuses on describing "what" you want to achieve rather than "how" to achieve it, using expressive query syntax.

Key Characteristics: - Query syntax over method syntax when appropriate - Descriptive variable names - Separation of concerns - Readable and self-documenting code

Coding Example:

public class DeclarativeLinqExample
{
    public IEnumerable<SalesReport> GenerateSalesReport(IEnumerable<Order> orders)
    {
        // Declarative approach - describes what we want, not how to get it
        var salesReport = from order in orders
                         where order.Status == OrderStatus.Completed
                         group order by new { order.Region, order.ProductCategory } into groupedOrders
                         select new SalesReport
                         {
                             Region = groupedOrders.Key.Region,
                             ProductCategory = groupedOrders.Key.ProductCategory,
                             TotalSales = groupedOrders.Sum(o => o.Amount),
                             OrderCount = groupedOrders.Count(),
                             AverageOrderValue = groupedOrders.Average(o => o.Amount),
                             TopProducts = (from o in groupedOrders
                                           group o by o.ProductId into productGroup
                                           select new ProductSales
                                           {
                                               ProductId = productGroup.Key,
                                               TotalSold = productGroup.Sum(p => p.Quantity),
                                               Revenue = productGroup.Sum(p => p.Amount)
                                           })
                                           .OrderByDescending(p => p.Revenue)
                                           .Take(5)
                                           .ToList()
                         };

        return salesReport.OrderByDescending(r => r.TotalSales);
    }

    // Declarative data validation
    public ValidationResult ValidateOrders(IEnumerable<Order> orders)
    {
        var validationRules = new[]
        {
            new ValidationRule("Amount must be positive", 
                orders.All(o => o.Amount > 0)),
            new ValidationRule("Customer must exist", 
                orders.All(o => !string.IsNullOrEmpty(o.CustomerId))),
            new ValidationRule("Order date cannot be in future", 
                orders.All(o => o.OrderDate <= DateTime.UtcNow))
        };

        var failedRules = validationRules.Where(rule => !rule.IsValid);

        return new ValidationResult
        {
            IsValid = !failedRules.Any(),
            Errors = failedRules.Select(r => r.Message).ToList()
        };
    }
}

89. How do you implement LINQ imperative programming?

Answer: LINQ imperative programming uses method chaining and explicit control flow to achieve desired results, focusing on "how" to perform operations.

Key Characteristics: - Method syntax with explicit method calls - Step-by-step data transformation - Explicit error handling - Performance optimization through method ordering

Coding Example:

public class ImperativeLinqExample
{
    public List<ProcessedOrder> ProcessOrdersImperatively(IEnumerable<Order> orders)
    {
        // Imperative approach - explicit step-by-step processing
        var processedOrders = orders
            .Where(order => order.Status == OrderStatus.Pending)
            .Select(order => new
            {
                Order = order,
                Priority = CalculatePriority(order),
                ProcessingTime = EstimateProcessingTime(order)
            })
            .OrderByDescending(x => x.Priority)
            .ThenBy(x => x.ProcessingTime)
            .Select(x => new ProcessedOrder
            {
                OrderId = x.Order.Id,
                CustomerId = x.Order.CustomerId,
                Priority = x.Priority,
                EstimatedProcessingTime = x.ProcessingTime,
                ProcessingSteps = GenerateProcessingSteps(x.Order)
            })
            .ToList();

        // Explicit error handling
        var invalidOrders = processedOrders
            .Where(order => !ValidateProcessedOrder(order))
            .ToList();

        if (invalidOrders.Any())
        {
            LogInvalidOrders(invalidOrders);
            processedOrders.RemoveAll(order => invalidOrders.Contains(order));
        }

        return processedOrders;
    }

    private int CalculatePriority(Order order)
    {
        return order.Amount > 1000 ? 1 : 
               order.IsVipCustomer ? 2 : 3;
    }

    private TimeSpan EstimateProcessingTime(Order order)
    {
        return TimeSpan.FromMinutes(order.Amount / 100);
    }

    private List<string> GenerateProcessingSteps(Order order)
    {
        var steps = new List<string>();

        if (order.RequiresApproval)
            steps.Add("Awaiting approval");

        if (order.RequiresInventoryCheck)
            steps.Add("Checking inventory");

        steps.Add("Processing payment");
        steps.Add("Preparing shipment");

        return steps;
    }
}

90. How do you handle LINQ hybrid approaches?

Answer: LINQ hybrid approaches combine multiple programming paradigms to leverage the strengths of each approach for different parts of the solution.

Key Strategies: - Use declarative queries for data retrieval - Apply imperative processing for complex business logic - Implement functional patterns for data transformation - Combine event-driven patterns for real-time processing

Coding Example:

public class HybridLinqExample
{
    private readonly IOrderRepository _orderRepository;
    private readonly Subject<OrderEvent> _orderEvents;

    public HybridLinqExample(IOrderRepository orderRepository)
    {
        _orderRepository = orderRepository;
        _orderEvents = new Subject<OrderEvent>();
    }

    public async Task<OrderAnalytics> GenerateOrderAnalyticsAsync(
        DateTime startDate, 
        DateTime endDate)
    {
        // Declarative: Data retrieval and basic aggregation
        var orders = await _orderRepository.GetOrdersAsync(startDate, endDate);

        var basicAnalytics = from order in orders
                            group order by order.CustomerId into customerGroup
                            select new CustomerAnalytics
                            {
                                CustomerId = customerGroup.Key,
                                TotalOrders = customerGroup.Count(),
                                TotalAmount = customerGroup.Sum(o => o.Amount),
                                AverageOrderValue = customerGroup.Average(o => o.Amount)
                            };

        // Functional: Data transformation and filtering
        var topCustomers = basicAnalytics
            .Where(c => c.TotalAmount > 10000)
            .OrderByDescending(c => c.TotalAmount)
            .Take(10)
            .ToList();

        // Imperative: Complex business logic
        var riskAssessment = await AssessCustomerRiskAsync(topCustomers);

        // Event-driven: Real-time monitoring
        var realTimeMetrics = _orderEvents
            .Where(e => e.Timestamp >= startDate && e.Timestamp <= endDate)
            .Buffer(TimeSpan.FromMinutes(5))
            .Select(events => new RealTimeMetrics
            {
                EventCount = events.Count,
                TotalValue = events.Sum(e => e.Amount),
                AverageValue = events.Average(e => e.Amount)
            });

        return new OrderAnalytics
        {
            CustomerAnalytics = basicAnalytics.ToList(),
            TopCustomers = topCustomers,
            RiskAssessment = riskAssessment,
            RealTimeMetrics = realTimeMetrics
        };
    }

    private async Task<RiskAssessment> AssessCustomerRiskAsync(
        IEnumerable<CustomerAnalytics> customers)
    {
        // Imperative business logic
        var riskAssessment = new RiskAssessment();

        foreach (var customer in customers)
        {
            var riskScore = await CalculateRiskScoreAsync(customer);
            riskAssessment.CustomerRisks.Add(new CustomerRisk
            {
                CustomerId = customer.CustomerId,
                RiskScore = riskScore,
                RiskLevel = DetermineRiskLevel(riskScore)
            });
        }

        return riskAssessment;
    }
}

Monitoring & Diagnostics

91. How do you implement LINQ logging?

Answer: LINQ logging involves capturing query execution details, performance metrics, and debugging information for LINQ operations.

Key Strategies: - Query interception and logging - Performance timing - Query plan analysis - Error logging with context

Coding Example:

public class LinqLoggingExample
{
    private readonly ILogger<LinqLoggingExample> _logger;

    public LinqLoggingExample(ILogger<LinqLoggingExample> logger)
    {
        _logger = logger;
    }

    public async Task<IEnumerable<Order>> GetOrdersWithLoggingAsync(
        Expression<Func<Order, bool>> filter)
    {
        var stopwatch = Stopwatch.StartNew();

        try
        {
            _logger.LogInformation("Starting LINQ query execution with filter: {Filter}", 
                filter.ToString());

            var query = _context.Orders
                .Where(filter)
                .Include(o => o.Customer)
                .Include(o => o.OrderItems);

            // Log the generated SQL
            var sql = query.ToQueryString();
            _logger.LogDebug("Generated SQL: {SQL}", sql);

            var result = await query.ToListAsync();

            stopwatch.Stop();

            _logger.LogInformation("LINQ query completed successfully. " +
                "Retrieved {Count} orders in {ElapsedMs}ms", 
                result.Count, stopwatch.ElapsedMilliseconds);

            return result;
        }
        catch (Exception ex)
        {
            stopwatch.Stop();
            _logger.LogError(ex, "LINQ query failed after {ElapsedMs}ms. " +
                "Filter: {Filter}", stopwatch.ElapsedMilliseconds, filter.ToString());
            throw;
        }
    }

    // Custom LINQ provider with logging
    public class LoggingQueryProvider : IQueryProvider
    {
        private readonly IQueryProvider _innerProvider;
        private readonly ILogger _logger;

        public LoggingQueryProvider(IQueryProvider innerProvider, ILogger logger)
        {
            _innerProvider = innerProvider;
            _logger = logger;
        }

        public IQueryable<TElement> CreateQuery<TElement>(Expression expression)
        {
            _logger.LogDebug("Creating query for type {Type}", typeof(TElement).Name);
            return new LoggingQueryable<TElement>(_innerProvider.CreateQuery<TElement>(expression), _logger);
        }

        public IQueryable CreateQuery(Expression expression)
        {
            _logger.LogDebug("Creating query for expression: {Expression}", expression.ToString());
            return _innerProvider.CreateQuery(expression);
        }

        public TResult Execute<TResult>(Expression expression)
        {
            var stopwatch = Stopwatch.StartNew();
            try
            {
                _logger.LogDebug("Executing query: {Expression}", expression.ToString());
                var result = _innerProvider.Execute<TResult>(expression);
                stopwatch.Stop();
                _logger.LogInformation("Query executed successfully in {ElapsedMs}ms", 
                    stopwatch.ElapsedMilliseconds);
                return result;
            }
            catch (Exception ex)
            {
                stopwatch.Stop();
                _logger.LogError(ex, "Query execution failed after {ElapsedMs}ms", 
                    stopwatch.ElapsedMilliseconds);
                throw;
            }
        }
    }
}

92. How do you handle LINQ performance monitoring?

Answer: LINQ performance monitoring involves tracking query execution times, resource usage, and identifying performance bottlenecks.

Key Metrics: - Query execution time - Memory usage - Database round trips - Query plan efficiency - Resource consumption

Coding Example:

public class LinqPerformanceMonitor
{
    private readonly ILogger<LinqPerformanceMonitor> _logger;
    private readonly IMetricsCollector _metrics;

    public LinqPerformanceMonitor(ILogger<LinqPerformanceMonitor> logger, 
        IMetricsCollector metrics)
    {
        _logger = logger;
        _metrics = metrics;
    }

    public async Task<IEnumerable<T>> MonitorQueryPerformanceAsync<T>(
        IQueryable<T> query, 
        string operationName)
    {
        var performanceContext = new QueryPerformanceContext(operationName);

        try
        {
            // Monitor memory before query
            var memoryBefore = GC.GetTotalMemory(false);

            // Execute query with timing
            var stopwatch = Stopwatch.StartNew();
            var result = await query.ToListAsync();
            stopwatch.Stop();

            // Monitor memory after query
            var memoryAfter = GC.GetTotalMemory(false);
            var memoryUsed = memoryAfter - memoryBefore;

            // Record metrics
            _metrics.RecordQueryExecutionTime(operationName, stopwatch.ElapsedMilliseconds);
            _metrics.RecordQueryMemoryUsage(operationName, memoryUsed);
            _metrics.RecordQueryResultCount(operationName, result.Count);

            // Log performance details
            _logger.LogInformation(
                "Query '{OperationName}' completed: {ResultCount} items, " +
                "{ElapsedMs}ms, {MemoryUsed} bytes", 
                operationName, result.Count, stopwatch.ElapsedMilliseconds, memoryUsed);

            // Alert on slow queries
            if (stopwatch.ElapsedMilliseconds > 1000)
            {
                _logger.LogWarning("Slow query detected: {OperationName} took {ElapsedMs}ms", 
                    operationName, stopwatch.ElapsedMilliseconds);
            }

            return result;
        }
        catch (Exception ex)
        {
            _metrics.RecordQueryError(operationName);
            _logger.LogError(ex, "Query '{OperationName}' failed", operationName);
            throw;
        }
    }

    // Performance monitoring middleware
    public class LinqPerformanceMiddleware
    {
        private readonly RequestDelegate _next;
        private readonly LinqPerformanceMonitor _monitor;

        public LinqPerformanceMiddleware(RequestDelegate next, 
            LinqPerformanceMonitor monitor)
        {
            _next = next;
            _monitor = monitor;
        }

        public async Task InvokeAsync(HttpContext context)
        {
            var originalBodyStream = context.Response.Body;

            using var memoryStream = new MemoryStream();
            context.Response.Body = memoryStream;

            var stopwatch = Stopwatch.StartNew();

            try
            {
                await _next(context);
                stopwatch.Stop();

                // Monitor LINQ operations in the request
                var linqOperations = context.Items["LinqOperations"] as List<string> ?? new List<string>();

                foreach (var operation in linqOperations)
                {
                    _monitor._metrics.RecordQueryExecutionTime(operation, stopwatch.ElapsedMilliseconds);
                }
            }
            finally
            {
                memoryStream.Position = 0;
                await memoryStream.CopyToAsync(originalBodyStream);
            }
        }
    }
}

93. How do you implement LINQ query analysis?

Answer: LINQ query analysis involves examining query structure, execution plans, and optimization opportunities.

Key Analysis Areas: - Query complexity analysis - Execution plan examination - Performance bottleneck identification - Query optimization suggestions

Coding Example:

public class LinqQueryAnalyzer
{
    private readonly ILogger<LinqQueryAnalyzer> _logger;

    public LinqQueryAnalyzer(ILogger<LinqQueryAnalyzer> logger)
    {
        _logger = logger;
    }

    public QueryAnalysisResult AnalyzeQuery<T>(IQueryable<T> query, string queryName)
    {
        var analysis = new QueryAnalysisResult
        {
            QueryName = queryName,
            AnalysisTimestamp = DateTime.UtcNow
        };

        try
        {
            // Analyze query structure
            analysis.QueryStructure = AnalyzeQueryStructure(query.Expression);

            // Get execution plan
            analysis.ExecutionPlan = GetExecutionPlan(query);

            // Analyze complexity
            analysis.ComplexityScore = CalculateComplexityScore(query.Expression);

            // Identify potential issues
            analysis.PotentialIssues = IdentifyPotentialIssues(query);

            // Generate optimization suggestions
            analysis.OptimizationSuggestions = GenerateOptimizationSuggestions(query);

            _logger.LogInformation("Query analysis completed for '{QueryName}'. " +
                "Complexity score: {ComplexityScore}", queryName, analysis.ComplexityScore);

            return analysis;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Failed to analyze query '{QueryName}'", queryName);
            throw;
        }
    }

    private QueryStructure AnalyzeQueryStructure(Expression expression)
    {
        var visitor = new QueryStructureVisitor();
        visitor.Visit(expression);

        return new QueryStructure
        {
            WhereClauses = visitor.WhereClauses,
            OrderByClauses = visitor.OrderByClauses,
            SelectClauses = visitor.SelectClauses,
            JoinClauses = visitor.JoinClauses,
            GroupByClauses = visitor.GroupByClauses
        };
    }

    private List<QueryIssue> IdentifyPotentialIssues<T>(IQueryable<T> query)
    {
        var issues = new List<QueryIssue>();

        // Check for N+1 query problems
        if (HasNPlusOnePotential(query))
        {
            issues.Add(new QueryIssue
            {
                Type = IssueType.NPlusOneQuery,
                Severity = IssueSeverity.High,
                Description = "Potential N+1 query detected. Consider using Include() or projection."
            });
        }

        // Check for missing indexes
        if (HasMissingIndexPotential(query))
        {
            issues.Add(new QueryIssue
            {
                Type = IssueType.MissingIndex,
                Severity = IssueSeverity.Medium,
                Description = "Query may benefit from additional database indexes."
            });
        }

        // Check for large result sets
        if (HasLargeResultSetPotential(query))
        {
            issues.Add(new QueryIssue
            {
                Type = IssueType.LargeResultSet,
                Severity = IssueSeverity.Medium,
                Description = "Query may return large result set. Consider pagination."
            });
        }

        return issues;
    }

    private List<OptimizationSuggestion> GenerateOptimizationSuggestions<T>(IQueryable<T> query)
    {
        var suggestions = new List<OptimizationSuggestion>();

        // Suggest pagination for large queries
        if (ShouldSuggestPagination(query))
        {
            suggestions.Add(new OptimizationSuggestion
            {
                Type = OptimizationType.Pagination,
                Description = "Consider implementing pagination to limit result set size.",
                CodeExample = "query.Skip(pageSize * pageNumber).Take(pageSize)"
            });
        }

        // Suggest projection for performance
        if (ShouldSuggestProjection(query))
        {
            suggestions.Add(new OptimizationSuggestion
            {
                Type = OptimizationType.Projection,
                Description = "Use projection to select only required fields.",
                CodeExample = "query.Select(o => new { o.Id, o.Name, o.Amount })"
            });
        }

        return suggestions;
    }
}

public class QueryAnalysisResult
{
    public string QueryName { get; set; }
    public DateTime AnalysisTimestamp { get; set; }
    public QueryStructure QueryStructure { get; set; }
    public string ExecutionPlan { get; set; }
    public int ComplexityScore { get; set; }
    public List<QueryIssue> PotentialIssues { get; set; }
    public List<OptimizationSuggestion> OptimizationSuggestions { get; set; }
}

94. How do you handle LINQ error tracking?

Answer: LINQ error tracking involves capturing, categorizing, and analyzing errors that occur during LINQ query execution.

Key Strategies: - Comprehensive error logging - Error categorization and classification - Error context preservation - Error recovery mechanisms

Coding Example:

public class LinqErrorTracker
{
    private readonly ILogger<LinqErrorTracker> _logger;
    private readonly IErrorReportingService _errorReporting;

    public LinqErrorTracker(ILogger<LinqErrorTracker> logger, 
        IErrorReportingService errorReporting)
    {
        _logger = logger;
        _errorReporting = errorReporting;
    }

    public async Task<IEnumerable<T>> ExecuteWithErrorTrackingAsync<T>(
        IQueryable<T> query, 
        string operationName,
        Dictionary<string, object> context = null)
    {
        var errorContext = new ErrorContext
        {
            OperationName = operationName,
            QueryString = query.ToString(),
            Timestamp = DateTime.UtcNow,
            AdditionalContext = context ?? new Dictionary<string, object>()
        };

        try
        {
            var result = await query.ToListAsync();
            return result;
        }
        catch (InvalidOperationException ex)
        {
            await HandleLinqErrorAsync(ex, errorContext, ErrorType.InvalidOperation);
            throw;
        }
        catch (NotSupportedException ex)
        {
            await HandleLinqErrorAsync(ex, errorContext, ErrorType.NotSupported);
            throw;
        }
        catch (SqlException ex)
        {
            await HandleLinqErrorAsync(ex, errorContext, ErrorType.DatabaseError);
            throw;
        }
        catch (Exception ex)
        {
            await HandleLinqErrorAsync(ex, errorContext, ErrorType.Unknown);
            throw;
        }
    }

    private async Task HandleLinqErrorAsync(Exception ex, ErrorContext context, ErrorType errorType)
    {
        var errorInfo = new LinqErrorInfo
        {
            ErrorType = errorType,
            ErrorMessage = ex.Message,
            StackTrace = ex.StackTrace,
            InnerException = ex.InnerException?.Message,
            Context = context,
            Severity = DetermineErrorSeverity(errorType, ex)
        };

        // Log error details
        _logger.LogError(ex, "LINQ error in operation '{OperationName}': {ErrorMessage}", 
            context.OperationName, ex.Message);

        // Report to error tracking service
        await _errorReporting.ReportErrorAsync(errorInfo);

        // Store error for analysis
        await StoreErrorForAnalysisAsync(errorInfo);

        // Alert on critical errors
        if (errorInfo.Severity == ErrorSeverity.Critical)
        {
            await SendCriticalErrorAlertAsync(errorInfo);
        }
    }

    private ErrorSeverity DetermineErrorSeverity(ErrorType errorType, Exception ex)
    {
        return errorType switch
        {
            ErrorType.DatabaseError => ErrorSeverity.Critical,
            ErrorType.InvalidOperation => ErrorSeverity.High,
            ErrorType.NotSupported => ErrorSeverity.Medium,
            _ => ErrorSeverity.Low
        };
    }

    // Error recovery mechanisms
    public async Task<IEnumerable<T>> ExecuteWithRetryAsync<T>(
        Func<Task<IEnumerable<T>>> queryFunc,
        int maxRetries = 3,
        TimeSpan delay = default)
    {
        var retryPolicy = Policy
            .Handle<SqlException>()
            .Or<TimeoutException>()
            .WaitAndRetryAsync(maxRetries, 
                retryAttempt => delay == default ? 
                    TimeSpan.FromSeconds(Math.Pow(2, retryAttempt)) : delay,
                onRetry: (exception, timeSpan, retryCount, context) =>
                {
                    _logger.LogWarning("Retry {RetryCount} for query after {Delay}ms due to {Error}", 
                        retryCount, timeSpan.TotalMilliseconds, exception.Message);
                });

        return await retryPolicy.ExecuteAsync(queryFunc);
    }
}

public class LinqErrorInfo
{
    public ErrorType ErrorType { get; set; }
    public string ErrorMessage { get; set; }
    public string StackTrace { get; set; }
    public string InnerException { get; set; }
    public ErrorContext Context { get; set; }
    public ErrorSeverity Severity { get; set; }
    public DateTime Timestamp { get; set; } = DateTime.UtcNow;
}

public enum ErrorType
{
    InvalidOperation,
    NotSupported,
    DatabaseError,
    Timeout,
    Unknown
}

public enum ErrorSeverity
{
    Low,
    Medium,
    High,
    Critical
}

95. How do you implement LINQ health checks?

Answer: LINQ health checks involve monitoring the health and performance of LINQ operations to ensure system reliability.

Key Health Metrics: - Query success rates - Response times - Resource utilization - Error rates - Connection pool health

Coding Example:

public class LinqHealthChecker : IHealthCheck
{
    private readonly DbContext _context;
    private readonly ILogger<LinqHealthChecker> _logger;
    private readonly IMetricsCollector _metrics;

    public LinqHealthChecker(DbContext context, ILogger<LinqHealthChecker> logger, 
        IMetricsCollector metrics)
    {
        _context = context;
        _logger = logger;
        _metrics = metrics;
    }

    public async Task<HealthCheckResult> CheckHealthAsync(
        HealthCheckContext context, 
        CancellationToken cancellationToken = default)
    {
        var healthData = new Dictionary<string, object>();
        var issues = new List<string>();

        try
        {
            // Check database connectivity
            var connectivityResult = await CheckDatabaseConnectivityAsync();
            healthData["DatabaseConnectivity"] = connectivityResult.IsHealthy;

            if (!connectivityResult.IsHealthy)
            {
                issues.Add($"Database connectivity: {connectivityResult.Description}");
            }

            // Check query performance
            var performanceResult = await CheckQueryPerformanceAsync();
            healthData["QueryPerformance"] = performanceResult.IsHealthy;
            healthData["AverageQueryTime"] = performanceResult.AverageQueryTime;

            if (!performanceResult.IsHealthy)
            {
                issues.Add($"Query performance: {performanceResult.Description}");
            }

            // Check connection pool health
            var connectionPoolResult = await CheckConnectionPoolHealthAsync();
            healthData["ConnectionPoolHealth"] = connectionPoolResult.IsHealthy;
            healthData["ActiveConnections"] = connectionPoolResult.ActiveConnections;
            healthData["AvailableConnections"] = connectionPoolResult.AvailableConnections;

            if (!connectionPoolResult.IsHealthy)
            {
                issues.Add($"Connection pool: {connectionPoolResult.Description}");
            }

            // Check error rates
            var errorRateResult = await CheckErrorRatesAsync();
            healthData["ErrorRate"] = errorRateResult.ErrorRate;
            healthData["ErrorRateThreshold"] = errorRateResult.Threshold;

            if (errorRateResult.ErrorRate > errorRateResult.Threshold)
            {
                issues.Add($"High error rate: {errorRateResult.ErrorRate:P2}");
            }

            var isHealthy = !issues.Any();
            var status = isHealthy ? HealthStatus.Healthy : HealthStatus.Unhealthy;

            _logger.LogInformation("LINQ health check completed. Status: {Status}, Issues: {IssueCount}", 
                status, issues.Count);

            return new HealthCheckResult(status, 
                description: isHealthy ? "All LINQ operations are healthy" : 
                    $"LINQ health issues detected: {string.Join(", ", issues)}",
                data: healthData);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "LINQ health check failed");
            return HealthCheckResult.Unhealthy("LINQ health check failed", ex);
        }
    }

    private async Task<ConnectivityResult> CheckDatabaseConnectivityAsync()
    {
        var stopwatch = Stopwatch.StartNew();

        try
        {
            await _context.Database.OpenConnectionAsync();
            await _context.Database.CloseConnectionAsync();

            stopwatch.Stop();

            return new ConnectivityResult
            {
                IsHealthy = true,
                ResponseTime = stopwatch.ElapsedMilliseconds,
                Description = $"Database connection successful in {stopwatch.ElapsedMilliseconds}ms"
            };
        }
        catch (Exception ex)
        {
            stopwatch.Stop();
            return new ConnectivityResult
            {
                IsHealthy = false,
                ResponseTime = stopwatch.ElapsedMilliseconds,
                Description = $"Database connection failed: {ex.Message}"
            };
        }
    }

    private async Task<PerformanceResult> CheckQueryPerformanceAsync()
    {
        var testQueries = new[]
        {
            _context.Set<Order>().Take(1),
            _context.Set<Customer>().Take(1),
            _context.Set<Order>().Where(o => o.Id > 0).Take(10)
        };

        var queryTimes = new List<long>();

        foreach (var query in testQueries)
        {
            var stopwatch = Stopwatch.StartNew();
            try
            {
                await query.ToListAsync();
                stopwatch.Stop();
                queryTimes.Add(stopwatch.ElapsedMilliseconds);
            }
            catch
            {
                stopwatch.Stop();
                queryTimes.Add(stopwatch.ElapsedMilliseconds);
            }
        }

        var averageTime = queryTimes.Average();
        var isHealthy = averageTime < 1000; // 1 second threshold

        return new PerformanceResult
        {
            IsHealthy = isHealthy,
            AverageQueryTime = averageTime,
            Description = $"Average query time: {averageTime:F2}ms"
        };
    }
}

public class ConnectivityResult
{
    public bool IsHealthy { get; set; }
    public long ResponseTime { get; set; }
    public string Description { get; set; }
}

public class PerformanceResult
{
    public bool IsHealthy { get; set; }
    public double AverageQueryTime { get; set; }
    public string Description { get; set; }
}

96. How do you handle LINQ metrics collection?

Answer: LINQ metrics collection involves gathering quantitative data about LINQ operations for analysis and optimization.

Key Metrics: - Query execution times - Result set sizes - Memory usage - Database round trips - Cache hit rates

Coding Example:

public class LinqMetricsCollector
{
    private readonly IMetricsService _metricsService;
    private readonly ILogger<LinqMetricsCollector> _logger;

    public LinqMetricsCollector(IMetricsService metricsService, 
        ILogger<LinqMetricsCollector> logger)
    {
        _metricsService = metricsService;
        _logger = logger;
    }

    public async Task<IEnumerable<T>> CollectMetricsAsync<T>(
        IQueryable<T> query, 
        string operationName,
        Dictionary<string, string> tags = null)
    {
        var metricsContext = new MetricsContext
        {
            OperationName = operationName,
            StartTime = DateTime.UtcNow,
            Tags = tags ?? new Dictionary<string, string>()
        };

        var stopwatch = Stopwatch.StartNew();
        var memoryBefore = GC.GetTotalMemory(false);

        try
        {
            var result = await query.ToListAsync();

            stopwatch.Stop();
            var memoryAfter = GC.GetTotalMemory(false);

            await RecordMetricsAsync(metricsContext, stopwatch.ElapsedMilliseconds, 
                result.Count, memoryAfter - memoryBefore, true);

            return result;
        }
        catch (Exception ex)
        {
            stopwatch.Stop();
            await RecordMetricsAsync(metricsContext, stopwatch.ElapsedMilliseconds, 
                0, 0, false);
            throw;
        }
    }

    private async Task RecordMetricsAsync(MetricsContext context, long executionTime, 
        int resultCount, long memoryUsed, bool success)
    {
        var metrics = new[]
        {
            new Metric("linq.query.execution_time", executionTime, 
                context.Tags.Concat(new[] { new KeyValuePair<string, string>("operation", context.OperationName) })),
            new Metric("linq.query.result_count", resultCount, 
                context.Tags.Concat(new[] { new KeyValuePair<string, string>("operation", context.OperationName) })),
            new Metric("linq.query.memory_usage", memoryUsed, 
                context.Tags.Concat(new[] { new KeyValuePair<string, string>("operation", context.OperationName) })),
            new Metric("linq.query.success", success ? 1 : 0, 
                context.Tags.Concat(new[] { new KeyValuePair<string, string>("operation", context.OperationName) }))
        };

        foreach (var metric in metrics)
        {
            await _metricsService.RecordMetricAsync(metric);
        }

        // Record histogram for execution time
        await _metricsService.RecordHistogramAsync("linq.query.execution_time_histogram", 
            executionTime, context.Tags);

        // Record custom metrics
        await RecordCustomMetricsAsync(context, executionTime, resultCount, memoryUsed, success);
    }

    private async Task RecordCustomMetricsAsync(MetricsContext context, long executionTime, 
        int resultCount, long memoryUsed, bool success)
    {
        // Performance tier classification
        var performanceTier = executionTime switch
        {
            < 100 => "fast",
            < 1000 => "normal",
            < 5000 => "slow",
            _ => "very_slow"
        };

        await _metricsService.RecordMetricAsync(new Metric("linq.query.performance_tier", 1, 
            context.Tags.Concat(new[] 
            { 
                new KeyValuePair<string, string>("operation", context.OperationName),
                new KeyValuePair<string, string>("tier", performanceTier)
            })));

        // Memory efficiency
        var memoryEfficiency = resultCount > 0 ? memoryUsed / (double)resultCount : 0;
        await _metricsService.RecordMetricAsync(new Metric("linq.query.memory_efficiency", 
            memoryEfficiency, context.Tags));
    }

    // Real-time metrics dashboard
    public async Task<LinqMetricsDashboard> GetMetricsDashboardAsync(TimeSpan timeRange)
    {
        var endTime = DateTime.UtcNow;
        var startTime = endTime - timeRange;

        var metrics = await _metricsService.GetMetricsAsync(startTime, endTime);

        return new LinqMetricsDashboard
        {
            TimeRange = timeRange,
            TotalQueries = metrics.Count(m => m.Name == "linq.query.success"),
            SuccessfulQueries = metrics.Count(m => m.Name == "linq.query.success" && m.Value == 1),
            AverageExecutionTime = metrics
                .Where(m => m.Name == "linq.query.execution_time")
                .Average(m => m.Value),
            TotalMemoryUsage = metrics
                .Where(m => m.Name == "linq.query.memory_usage")
                .Sum(m => m.Value),
            PerformanceDistribution = await GetPerformanceDistributionAsync(metrics)
        };
    }
}

public class LinqMetricsDashboard
{
    public TimeSpan TimeRange { get; set; }
    public int TotalQueries { get; set; }
    public int SuccessfulQueries { get; set; }
    public double SuccessRate => TotalQueries > 0 ? (double)SuccessfulQueries / TotalQueries : 0;
    public double AverageExecutionTime { get; set; }
    public long TotalMemoryUsage { get; set; }
    public Dictionary<string, int> PerformanceDistribution { get; set; }
}

97. How do you implement LINQ debugging tools?

Answer: LINQ debugging tools help developers understand query execution, identify issues, and optimize performance.

Key Features: - Query visualization - Execution plan analysis - Step-by-step debugging - Performance profiling - Query comparison tools

Coding Example:

public class LinqDebuggingTools
{
    private readonly ILogger<LinqDebuggingTools> _logger;

    public LinqDebuggingTools(ILogger<LinqDebuggingTools> logger)
    {
        _logger = logger;
    }

    public async Task<QueryDebugInfo> DebugQueryAsync<T>(IQueryable<T> query, string queryName)
    {
        var debugInfo = new QueryDebugInfo
        {
            QueryName = queryName,
            Timestamp = DateTime.UtcNow
        };

        try
        {
            // Analyze query structure
            debugInfo.QueryStructure = AnalyzeQueryStructure(query.Expression);

            // Get SQL translation
            debugInfo.SqlTranslation = GetSqlTranslation(query);

            // Analyze execution plan
            debugInfo.ExecutionPlan = await AnalyzeExecutionPlanAsync(query);

            // Profile query execution
            debugInfo.ExecutionProfile = await ProfileQueryExecutionAsync(query);

            // Identify potential issues
            debugInfo.PotentialIssues = IdentifyDebugIssues(query, debugInfo);

            _logger.LogDebug("Query debugging completed for '{QueryName}'", queryName);

            return debugInfo;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Failed to debug query '{QueryName}'", queryName);
            throw;
        }
    }

    private QueryStructure AnalyzeQueryStructure(Expression expression)
    {
        var visitor = new QueryStructureAnalyzer();
        visitor.Visit(expression);

        return new QueryStructure
        {
            ExpressionType = expression.NodeType.ToString(),
            MethodCalls = visitor.MethodCalls,
            Parameters = visitor.Parameters,
            Constants = visitor.Constants,
            Complexity = CalculateExpressionComplexity(expression)
        };
    }

    private string GetSqlTranslation<T>(IQueryable<T> query)
    {
        try
        {
            return query.ToQueryString();
        }
        catch (Exception ex)
        {
            return $"Unable to translate to SQL: {ex.Message}";
        }
    }

    private async Task<ExecutionPlan> AnalyzeExecutionPlanAsync<T>(IQueryable<T> query)
    {
        var plan = new ExecutionPlan();

        try
        {
            // Use Entity Framework's query plan caching
            var compiledQuery = query.Compile();

            plan.IsCompiled = true;
            plan.CompilationTime = DateTime.UtcNow;

            // Analyze query complexity
            plan.ComplexityScore = CalculateQueryComplexity(query);

            // Estimate execution cost
            plan.EstimatedCost = EstimateExecutionCost(query);

        }
        catch (Exception ex)
        {
            plan.Errors.Add($"Execution plan analysis failed: {ex.Message}");
        }

        return plan;
    }

    private async Task<ExecutionProfile> ProfileQueryExecutionAsync<T>(IQueryable<T> query)
    {
        var profile = new ExecutionProfile();

        // Profile multiple executions for consistency
        var executionTimes = new List<long>();
        var memoryUsages = new List<long>();

        for (int i = 0; i < 3; i++)
        {
            var stopwatch = Stopwatch.StartNew();
            var memoryBefore = GC.GetTotalMemory(false);

            try
            {
                var result = await query.ToListAsync();

                stopwatch.Stop();
                var memoryAfter = GC.GetTotalMemory(false);

                executionTimes.Add(stopwatch.ElapsedMilliseconds);
                memoryUsages.Add(memoryAfter - memoryBefore);

                profile.ResultCount = result.Count;
            }
            catch (Exception ex)
            {
                stopwatch.Stop();
                profile.Errors.Add($"Execution {i + 1} failed: {ex.Message}");
            }
        }

        profile.AverageExecutionTime = executionTimes.Any() ? executionTimes.Average() : 0;
        profile.MinExecutionTime = executionTimes.Any() ? executionTimes.Min() : 0;
        profile.MaxExecutionTime = executionTimes.Any() ? executionTimes.Max() : 0;
        profile.AverageMemoryUsage = memoryUsages.Any() ? memoryUsages.Average() : 0;

        return profile;
    }

    // Interactive debugging session
    public async Task<DebugSession> StartDebugSessionAsync<T>(IQueryable<T> query, string sessionName)
    {
        var session = new DebugSession
        {
            SessionId = Guid.NewGuid(),
            SessionName = sessionName,
            StartTime = DateTime.UtcNow,
            Query = query
        };

        _logger.LogInformation("Started LINQ debug session '{SessionName}' with ID {SessionId}", 
            sessionName, session.SessionId);

        return session;
    }

    public async Task<StepResult> ExecuteDebugStepAsync(DebugSession session, int stepNumber)
    {
        var step = new StepResult
        {
            StepNumber = stepNumber,
            Timestamp = DateTime.UtcNow
        };

        try
        {
            // Execute query step by step
            var result = await session.Query.ToListAsync();

            step.ResultCount = result.Count;
            step.IsSuccessful = true;

            _logger.LogDebug("Debug step {StepNumber} completed successfully", stepNumber);
        }
        catch (Exception ex)
        {
            step.IsSuccessful = false;
            step.Error = ex.Message;

            _logger.LogError(ex, "Debug step {StepNumber} failed", stepNumber);
        }

        session.Steps.Add(step);
        return step;
    }
}

public class QueryDebugInfo
{
    public string QueryName { get; set; }
    public DateTime Timestamp { get; set; }
    public QueryStructure QueryStructure { get; set; }
    public string SqlTranslation { get; set; }
    public ExecutionPlan ExecutionPlan { get; set; }
    public ExecutionProfile ExecutionProfile { get; set; }
    public List<string> PotentialIssues { get; set; } = new List<string>();
}

public class DebugSession
{
    public Guid SessionId { get; set; }
    public string SessionName { get; set; }
    public DateTime StartTime { get; set; }
    public IQueryable Query { get; set; }
    public List<StepResult> Steps { get; set; } = new List<StepResult>();
}

98. How do you handle LINQ profiling?

Answer:

LINQ profiling involves monitoring and analyzing the performance of LINQ queries to identify bottlenecks, optimize execution plans, and ensure efficient data access patterns. As a technical lead, you need to implement systematic profiling strategies.

Key Approaches:

1. Query Execution Time Profiling

public class LinqProfiler
{
    private readonly ILogger<LinqProfiler> _logger;
    private readonly Dictionary<string, QueryMetrics> _queryMetrics;

    public LinqProfiler(ILogger<LinqProfiler> logger)
    {
        _logger = logger;
        _queryMetrics = new Dictionary<string, QueryMetrics>();
    }

    public async Task<T> ProfileQueryAsync<T>(string queryName, Func<Task<T>> query)
    {
        var stopwatch = Stopwatch.StartNew();
        var memoryBefore = GC.GetTotalMemory(false);

        try
        {
            var result = await query();
            stopwatch.Stop();

            var memoryAfter = GC.GetTotalMemory(false);
            var memoryUsed = memoryAfter - memoryBefore;

            RecordMetrics(queryName, stopwatch.ElapsedMilliseconds, memoryUsed);

            return result;
        }
        catch (Exception ex)
        {
            stopwatch.Stop();
            _logger.LogError(ex, "Query {QueryName} failed after {ElapsedMs}ms", 
                queryName, stopwatch.ElapsedMilliseconds);
            throw;
        }
    }

    private void RecordMetrics(string queryName, long executionTime, long memoryUsed)
    {
        if (!_queryMetrics.ContainsKey(queryName))
        {
            _queryMetrics[queryName] = new QueryMetrics();
        }

        var metrics = _queryMetrics[queryName];
        metrics.ExecutionCount++;
        metrics.TotalExecutionTime += executionTime;
        metrics.MaxExecutionTime = Math.Max(metrics.MaxExecutionTime, executionTime);
        metrics.TotalMemoryUsed += memoryUsed;

        if (executionTime > 1000) // Alert threshold
        {
            _logger.LogWarning("Slow query detected: {QueryName} took {ElapsedMs}ms", 
                queryName, executionTime);
        }
    }
}

public class QueryMetrics
{
    public int ExecutionCount { get; set; }
    public long TotalExecutionTime { get; set; }
    public long MaxExecutionTime { get; set; }
    public long TotalMemoryUsed { get; set; }
    public double AverageExecutionTime => ExecutionCount > 0 ? (double)TotalExecutionTime / ExecutionCount : 0;
}

2. Entity Framework Query Profiling

public class EfQueryProfiler : IInterceptor
{
    private readonly ILogger<EfQueryProfiler> _logger;
    private readonly ConcurrentDictionary<string, QueryStats> _queryStats;

    public EfQueryProfiler(ILogger<EfQueryProfiler> logger)
    {
        _logger = logger;
        _queryStats = new ConcurrentDictionary<string, QueryStats>();
    }

    public ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, 
        CommandEventData eventData, 
        InterceptionResult<DbDataReader> result, 
        CancellationToken cancellationToken = default)
    {
        var queryHash = GetQueryHash(command.CommandText);
        var stats = _queryStats.GetOrAdd(queryHash, _ => new QueryStats());

        stats.ExecutionCount++;
        stats.LastExecuted = DateTime.UtcNow;

        _logger.LogDebug("Executing query: {QueryHash} - {Sql}", 
            queryHash, command.CommandText);

        return new ValueTask<InterceptionResult<DbDataReader>>(result);
    }

    public ValueTask<InterceptionResult<DbDataReader>> ReaderExecutedAsync(
        DbCommand command, 
        CommandEventData eventData, 
        InterceptionResult<DbDataReader> result, 
        DbDataReader reader, 
        CancellationToken cancellationToken = default)
    {
        var queryHash = GetQueryHash(command.CommandText);
        var executionTime = eventData.Duration.TotalMilliseconds;

        if (_queryStats.TryGetValue(queryHash, out var stats))
        {
            stats.TotalExecutionTime += executionTime;
            stats.MaxExecutionTime = Math.Max(stats.MaxExecutionTime, executionTime);
        }

        if (executionTime > 500) // Performance threshold
        {
            _logger.LogWarning("Slow EF query detected: {QueryHash} took {ElapsedMs}ms", 
                queryHash, executionTime);
        }

        return new ValueTask<InterceptionResult<DbDataReader>>(result);
    }

    private string GetQueryHash(string sql) => 
        Convert.ToBase64String(SHA256.HashData(Encoding.UTF8.GetBytes(sql)));
}

public class QueryStats
{
    public int ExecutionCount { get; set; }
    public double TotalExecutionTime { get; set; }
    public double MaxExecutionTime { get; set; }
    public DateTime LastExecuted { get; set; }
}

3. LINQ Query Analysis with Expression Trees

public class LinqQueryAnalyzer
{
    public QueryAnalysisResult AnalyzeQuery<T>(IQueryable<T> query)
    {
        var expression = query.Expression;
        var analysis = new QueryAnalysisResult();

        AnalyzeExpression(expression, analysis);

        return analysis;
    }

    private void AnalyzeExpression(Expression expression, QueryAnalysisResult analysis)
    {
        switch (expression.NodeType)
        {
            case ExpressionType.Call:
                var methodCall = (MethodCallExpression)expression;
                analysis.Operations.Add(methodCall.Method.Name);

                if (methodCall.Method.Name == "Where")
                {
                    analysis.FilterCount++;
                }
                else if (methodCall.Method.Name == "OrderBy" || 
                         methodCall.Method.Name == "OrderByDescending")
                {
                    analysis.SortCount++;
                }
                else if (methodCall.Method.Name == "Select")
                {
                    analysis.ProjectionCount++;
                }

                foreach (var argument in methodCall.Arguments)
                {
                    AnalyzeExpression(argument, analysis);
                }
                break;

            case ExpressionType.Constant:
                var constant = (ConstantExpression)expression;
                if (constant.Value is IQueryable)
                {
                    analysis.SourceType = constant.Value.GetType().GetGenericArguments()[0];
                }
                break;
        }
    }
}

public class QueryAnalysisResult
{
    public Type SourceType { get; set; }
    public List<string> Operations { get; set; } = new();
    public int FilterCount { get; set; }
    public int SortCount { get; set; }
    public int ProjectionCount { get; set; }

    public bool HasPerformanceIssues => 
        FilterCount > 3 || SortCount > 2 || Operations.Contains("ToList");
}

99. How do you implement LINQ alerting?

Answer:

LINQ alerting involves setting up proactive monitoring and notification systems to detect performance issues, query failures, and other anomalies in LINQ operations. This is crucial for maintaining application performance and reliability.

Implementation Strategies:

1. Real-time Query Performance Alerting

public class LinqAlertingService : ILinqAlertingService
{
    private readonly ILogger<LinqAlertingService> _logger;
    private readonly IConfiguration _configuration;
    private readonly IEmailService _emailService;
    private readonly IAlertRepository _alertRepository;
    private readonly ConcurrentDictionary<string, AlertThreshold> _thresholds;

    public LinqAlertingService(
        ILogger<LinqAlertingService> logger,
        IConfiguration configuration,
        IEmailService emailService,
        IAlertRepository alertRepository)
    {
        _logger = logger;
        _configuration = configuration;
        _emailService = emailService;
        _alertRepository = alertRepository;
        _thresholds = LoadAlertThresholds();
    }

    public async Task MonitorQueryPerformanceAsync(string queryId, long executionTime, 
        int rowCount, string queryType)
    {
        var threshold = GetThreshold(queryType);

        if (executionTime > threshold.MaxExecutionTime)
        {
            await CreatePerformanceAlertAsync(queryId, executionTime, threshold.MaxExecutionTime, 
                "Execution time exceeded threshold");
        }

        if (rowCount > threshold.MaxRowCount)
        {
            await CreatePerformanceAlertAsync(queryId, executionTime, threshold.MaxRowCount, 
                "Row count exceeded threshold");
        }

        // Check for N+1 query patterns
        if (IsNPlusOneQuery(queryId, executionTime))
        {
            await CreatePerformanceAlertAsync(queryId, executionTime, 0, 
                "Potential N+1 query pattern detected");
        }
    }

    private async Task CreatePerformanceAlertAsync(string queryId, long executionTime, 
        long threshold, string reason)
    {
        var alert = new QueryPerformanceAlert
        {
            Id = Guid.NewGuid(),
            QueryId = queryId,
            ExecutionTime = executionTime,
            Threshold = threshold,
            Reason = reason,
            Timestamp = DateTime.UtcNow,
            Severity = DetermineSeverity(executionTime, threshold)
        };

        await _alertRepository.SaveAlertAsync(alert);

        if (alert.Severity >= AlertSeverity.High)
        {
            await SendImmediateAlertAsync(alert);
        }

        _logger.LogWarning("Query performance alert: {QueryId} - {Reason} - {ExecutionTime}ms", 
            queryId, reason, executionTime);
    }

    private AlertSeverity DetermineSeverity(long executionTime, long threshold)
    {
        var ratio = (double)executionTime / threshold;

        return ratio switch
        {
            >= 5.0 => AlertSeverity.Critical,
            >= 3.0 => AlertSeverity.High,
            >= 2.0 => AlertSeverity.Medium,
            _ => AlertSeverity.Low
        };
    }

    private async Task SendImmediateAlertAsync(QueryPerformanceAlert alert)
    {
        var message = new AlertMessage
        {
            Subject = $"LINQ Performance Alert - {alert.Severity}",
            Body = $@"
                Query ID: {alert.QueryId}
                Execution Time: {alert.ExecutionTime}ms
                Threshold: {alert.Threshold}ms
                Reason: {alert.Reason}
                Timestamp: {alert.Timestamp:yyyy-MM-dd HH:mm:ss}
            ",
            Recipients = GetAlertRecipients(alert.Severity)
        };

        await _emailService.SendAsync(message);
    }
}

public class QueryPerformanceAlert
{
    public Guid Id { get; set; }
    public string QueryId { get; set; }
    public long ExecutionTime { get; set; }
    public long Threshold { get; set; }
    public string Reason { get; set; }
    public DateTime Timestamp { get; set; }
    public AlertSeverity Severity { get; set; }
}

public enum AlertSeverity
{
    Low,
    Medium,
    High,
    Critical
}

2. Query Pattern Detection and Alerting

public class QueryPatternDetector
{
    private readonly ConcurrentDictionary<string, QueryExecutionHistory> _executionHistory;
    private readonly ILogger<QueryPatternDetector> _logger;

    public QueryPatternDetector(ILogger<QueryPatternDetector> logger)
    {
        _logger = logger;
        _executionHistory = new ConcurrentDictionary<string, QueryExecutionHistory>();
    }

    public async Task DetectAnomaliesAsync(string queryId, QueryExecutionInfo executionInfo)
    {
        var history = _executionHistory.GetOrAdd(queryId, _ => new QueryExecutionHistory());
        history.AddExecution(executionInfo);

        // Detect unusual patterns
        if (history.IsAnomalous())
        {
            await RaiseAnomalyAlertAsync(queryId, history);
        }

        // Detect memory leaks
        if (history.HasMemoryLeak())
        {
            await RaiseMemoryLeakAlertAsync(queryId, history);
        }

        // Detect connection pool exhaustion
        if (history.HasConnectionIssues())
        {
            await RaiseConnectionAlertAsync(queryId, history);
        }
    }

    private async Task RaiseAnomalyAlertAsync(string queryId, QueryExecutionHistory history)
    {
        var alert = new QueryAnomalyAlert
        {
            QueryId = queryId,
            AnomalyType = "Performance Degradation",
            BaselineMetrics = history.GetBaselineMetrics(),
            CurrentMetrics = history.GetCurrentMetrics(),
            Confidence = history.CalculateAnomalyConfidence()
        };

        await PublishAlertAsync(alert);
    }
}

public class QueryExecutionHistory
{
    private readonly Queue<QueryExecutionInfo> _recentExecutions;
    private readonly int _maxHistorySize = 100;

    public QueryExecutionHistory()
    {
        _recentExecutions = new Queue<QueryExecutionInfo>();
    }

    public void AddExecution(QueryExecutionInfo execution)
    {
        _recentExecutions.Enqueue(execution);

        if (_recentExecutions.Count > _maxHistorySize)
        {
            _recentExecutions.Dequeue();
        }
    }

    public bool IsAnomalous()
    {
        if (_recentExecutions.Count < 10) return false;

        var recent = _recentExecutions.TakeLast(5).ToList();
        var baseline = _recentExecutions.Take(_recentExecutions.Count - 5).ToList();

        var recentAvg = recent.Average(x => x.ExecutionTime);
        var baselineAvg = baseline.Average(x => x.ExecutionTime);

        return recentAvg > baselineAvg * 2; // 2x degradation threshold
    }

    public bool HasMemoryLeak()
    {
        var executions = _recentExecutions.ToList();
        if (executions.Count < 20) return false;

        // Check for increasing memory usage pattern
        var memoryTrend = CalculateTrend(executions.Select(x => x.MemoryUsed).ToList());
        return memoryTrend > 0.1; // 10% increase trend
    }

    private double CalculateTrend(List<long> values)
    {
        var n = values.Count;
        var sumX = n * (n + 1) / 2.0;
        var sumY = values.Sum();
        var sumXY = values.Select((y, i) => (i + 1) * y).Sum();
        var sumX2 = values.Select((_, i) => Math.Pow(i + 1, 2)).Sum();

        var slope = (n * sumXY - sumX * sumY) / (n * sumX2 - sumX * sumX);
        return slope;
    }
}

3. Automated Alert Resolution

public class LinqAlertResolver
{
    private readonly ILogger<LinqAlertResolver> _logger;
    private readonly IQueryOptimizer _queryOptimizer;
    private readonly IAlertRepository _alertRepository;

    public LinqAlertResolver(
        ILogger<LinqAlertResolver> logger,
        IQueryOptimizer queryOptimizer,
        IAlertRepository alertRepository)
    {
        _logger = logger;
        _queryOptimizer = queryOptimizer;
        _alertRepository = alertRepository;
    }

    public async Task<ResolutionResult> AttemptAutoResolutionAsync(QueryPerformanceAlert alert)
    {
        try
        {
            var resolution = await AnalyzeAndResolveAsync(alert);

            if (resolution.IsResolved)
            {
                await MarkAlertResolvedAsync(alert.Id, resolution);
                _logger.LogInformation("Auto-resolved alert {AlertId} with {Resolution}", 
                    alert.Id, resolution.ResolutionType);
            }

            return resolution;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Failed to auto-resolve alert {AlertId}", alert.Id);
            return new ResolutionResult { IsResolved = false, Error = ex.Message };
        }
    }

    private async Task<ResolutionResult> AnalyzeAndResolveAsync(QueryPerformanceAlert alert)
    {
        // Analyze query pattern and suggest optimizations
        var analysis = await _queryOptimizer.AnalyzeQueryAsync(alert.QueryId);

        if (analysis.CanOptimize)
        {
            var optimizedQuery = await _queryOptimizer.OptimizeQueryAsync(alert.QueryId);

            return new ResolutionResult
            {
                IsResolved = true,
                ResolutionType = "Query Optimization",
                OptimizedQuery = optimizedQuery,
                ExpectedImprovement = analysis.ExpectedImprovement
            };
        }

        // Check if it's a temporary issue
        if (IsTemporaryIssue(alert))
        {
            return new ResolutionResult
            {
                IsResolved = true,
                ResolutionType = "Temporary Issue",
                Resolution = "Issue appears to be temporary, monitoring continued"
            };
        }

        return new ResolutionResult { IsResolved = false };
    }

    private bool IsTemporaryIssue(QueryPerformanceAlert alert)
    {
        // Check if similar alerts occurred recently and resolved themselves
        var recentAlerts = _alertRepository.GetRecentAlertsAsync(alert.QueryId, TimeSpan.FromHours(1));
        return recentAlerts.Count() <= 2; // Not a persistent issue
    }
}

100. How do you handle LINQ troubleshooting?

Answer:

LINQ troubleshooting involves systematic debugging, root cause analysis, and resolution of LINQ-related issues. As a technical lead, you need to establish comprehensive troubleshooting methodologies and tools.

Troubleshooting Strategies:

1. Comprehensive LINQ Diagnostics

public class LinqDiagnosticsService : ILinqDiagnosticsService
{
    private readonly ILogger<LinqDiagnosticsService> _logger;
    private readonly IDbContextFactory<ApplicationDbContext> _contextFactory;
    private readonly IQueryAnalyzer _queryAnalyzer;

    public LinqDiagnosticsService(
        ILogger<LinqDiagnosticsService> logger,
        IDbContextFactory<ApplicationDbContext> contextFactory,
        IQueryAnalyzer queryAnalyzer)
    {
        _logger = logger;
        _contextFactory = contextFactory;
        _queryAnalyzer = queryAnalyzer;
    }

    public async Task<DiagnosticReport> DiagnoseQueryAsync<T>(IQueryable<T> query, 
        string queryName = null)
    {
        var report = new DiagnosticReport
        {
            QueryName = queryName ?? typeof(T).Name,
            Timestamp = DateTime.UtcNow,
            QueryType = typeof(T)
        };

        try
        {
            // Step 1: Analyze query structure
            report.QueryAnalysis = await AnalyzeQueryStructureAsync(query);

            // Step 2: Check for common issues
            report.Issues = await DetectCommonIssuesAsync(query);

            // Step 3: Performance analysis
            report.PerformanceMetrics = await AnalyzePerformanceAsync(query);

            // Step 4: Memory analysis
            report.MemoryAnalysis = await AnalyzeMemoryUsageAsync(query);

            // Step 5: Generate recommendations
            report.Recommendations = GenerateRecommendations(report);

            return report;
        }
        catch (Exception ex)
        {
            report.Errors.Add($"Diagnostic failed: {ex.Message}");
            _logger.LogError(ex, "Failed to diagnose query {QueryName}", queryName);
            return report;
        }
    }

    private async Task<QueryStructureAnalysis> AnalyzeQueryStructureAsync<T>(IQueryable<T> query)
    {
        var analysis = new QueryStructureAnalysis();

        // Parse expression tree
        var expression = query.Expression;
        analysis.ExpressionTree = ParseExpressionTree(expression);

        // Identify query components
        analysis.Components = IdentifyQueryComponents(expression);

        // Check for potential issues
        analysis.PotentialIssues = DetectStructuralIssues(expression);

        return analysis;
    }

    private async Task<List<QueryIssue>> DetectCommonIssuesAsync<T>(IQueryable<T> query)
    {
        var issues = new List<QueryIssue>();

        // Check for N+1 queries
        if (IsNPlusOneQuery(query))
        {
            issues.Add(new QueryIssue
            {
                Type = IssueType.NPlusOneQuery,
                Severity = IssueSeverity.High,
                Description = "Potential N+1 query pattern detected",
                Recommendation = "Consider using Include() or projection to load related data"
            });
        }

        // Check for missing indexes
        var missingIndexes = await CheckForMissingIndexesAsync(query);
        issues.AddRange(missingIndexes);

        // Check for inefficient projections
        if (HasInefficientProjection(query))
        {
            issues.Add(new QueryIssue
            {
                Type = IssueType.InefficientProjection,
                Severity = IssueSeverity.Medium,
                Description = "Query selects more data than necessary",
                Recommendation = "Use Select() to project only required fields"
            });
        }

        return issues;
    }

    private async Task<PerformanceMetrics> AnalyzePerformanceAsync<T>(IQueryable<T> query)
    {
        var metrics = new PerformanceMetrics();

        // Execute query with timing
        var stopwatch = Stopwatch.StartNew();
        var result = await query.ToListAsync();
        stopwatch.Stop();

        metrics.ExecutionTime = stopwatch.ElapsedMilliseconds;
        metrics.RowCount = result.Count;
        metrics.AverageRowSize = CalculateAverageRowSize(result);

        // Analyze query plan if possible
        metrics.QueryPlan = await GetQueryPlanAsync(query);

        return metrics;
    }

    private List<Recommendation> GenerateRecommendations(DiagnosticReport report)
    {
        var recommendations = new List<Recommendation>();

        foreach (var issue in report.Issues)
        {
            recommendations.Add(new Recommendation
            {
                Issue = issue,
                Priority = DeterminePriority(issue.Severity, report.PerformanceMetrics),
                ImplementationEffort = EstimateImplementationEffort(issue.Type),
                ExpectedImpact = EstimateExpectedImpact(issue.Type)
            });
        }

        return recommendations.OrderByDescending(r => r.Priority).ToList();
    }
}

public class DiagnosticReport
{
    public string QueryName { get; set; }
    public DateTime Timestamp { get; set; }
    public Type QueryType { get; set; }
    public QueryStructureAnalysis QueryAnalysis { get; set; }
    public List<QueryIssue> Issues { get; set; } = new();
    public PerformanceMetrics PerformanceMetrics { get; set; }
    public MemoryAnalysis MemoryAnalysis { get; set; }
    public List<Recommendation> Recommendations { get; set; } = new();
    public List<string> Errors { get; set; } = new();
}

2. Advanced Query Debugging Tools

public class LinqDebugger
{
    private readonly ILogger<LinqDebugger> _logger;
    private readonly IDbContextFactory<ApplicationDbContext> _contextFactory;

    public LinqDebugger(
        ILogger<LinqDebugger> logger,
        IDbContextFactory<ApplicationDbContext> contextFactory)
    {
        _logger = logger;
        _contextFactory = contextFactory;
    }

    public async Task<DebugSession> StartDebugSessionAsync<T>(IQueryable<T> query, 
        string sessionName)
    {
        var session = new DebugSession
        {
            Id = Guid.NewGuid(),
            Name = sessionName,
            StartTime = DateTime.UtcNow,
            QueryType = typeof(T)
        };

        // Enable detailed logging
        using var context = await _contextFactory.CreateDbContextAsync();
        context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

        // Enable query logging
        context.Database.SetCommandTimeout(TimeSpan.FromMinutes(5));

        session.Context = context;
        session.OriginalQuery = query;

        _logger.LogInformation("Started debug session {SessionId} for query {QueryName}", 
            session.Id, sessionName);

        return session;
    }

    public async Task<DebugStepResult> ExecuteDebugStepAsync(DebugSession session, 
        DebugStep step)
    {
        var result = new DebugStepResult
        {
            StepId = step.Id,
            Timestamp = DateTime.UtcNow
        };

        try
        {
            switch (step.Type)
            {
                case DebugStepType.ExecuteQuery:
                    result = await ExecuteQueryStepAsync(session, step);
                    break;

                case DebugStepType.AnalyzeExpression:
                    result = await AnalyzeExpressionStepAsync(session, step);
                    break;

                case DebugStepType.CheckData:
                    result = await CheckDataStepAsync(session, step);
                    break;

                case DebugStepType.OptimizeQuery:
                    result = await OptimizeQueryStepAsync(session, step);
                    break;
            }

            session.Steps.Add(step);
            session.Results.Add(result);

            return result;
        }
        catch (Exception ex)
        {
            result.Error = ex.Message;
            result.Success = false;
            _logger.LogError(ex, "Debug step {StepId} failed in session {SessionId}", 
                step.Id, session.Id);
            return result;
        }
    }

    private async Task<DebugStepResult> ExecuteQueryStepAsync(DebugSession session, 
        DebugStep step)
    {
        var result = new DebugStepResult { StepId = step.Id };

        var stopwatch = Stopwatch.StartNew();
        var query = step.Parameters["query"] as IQueryable;

        // Execute with detailed logging
        var sql = query.ToQueryString();
        result.Data["GeneratedSQL"] = sql;

        var data = await query.ToListAsync();
        stopwatch.Stop();

        result.Data["ExecutionTime"] = stopwatch.ElapsedMilliseconds;
        result.Data["RowCount"] = data.Count;
        result.Data["ResultType"] = data.GetType().Name;

        return result;
    }

    private async Task<DebugStepResult> AnalyzeExpressionStepAsync(DebugSession session, 
        DebugStep step)
    {
        var result = new DebugStepResult { StepId = step.Id };

        var query = step.Parameters["query"] as IQueryable;
        var expression = query.Expression;

        // Analyze expression tree
        var analysis = AnalyzeExpressionTree(expression);
        result.Data["ExpressionAnalysis"] = analysis;

        // Check for potential optimizations
        var optimizations = FindOptimizationOpportunities(expression);
        result.Data["Optimizations"] = optimizations;

        return result;
    }
}

public class DebugSession
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public DateTime StartTime { get; set; }
    public Type QueryType { get; set; }
    public DbContext Context { get; set; }
    public IQueryable OriginalQuery { get; set; }
    public List<DebugStep> Steps { get; set; } = new();
    public List<DebugStepResult> Results { get; set; } = new();
}

3. Automated Troubleshooting Workflows

public class LinqTroubleshootingWorkflow
{
    private readonly ILogger<LinqTroubleshootingWorkflow> _logger;
    private readonly ILinqDiagnosticsService _diagnosticsService;
    private readonly ILinqAlertingService _alertingService;
    private readonly IQueryOptimizer _queryOptimizer;

    public LinqTroubleshootingWorkflow(
        ILogger<LinqTroubleshootingWorkflow> logger,
        ILinqDiagnosticsService diagnosticsService,
        ILinqAlertingService alertingService,
        IQueryOptimizer queryOptimizer)
    {
        _logger = logger;
        _diagnosticsService = diagnosticsService;
        _alertingService = alertingService;
        _queryOptimizer = queryOptimizer;
    }

    public async Task<TroubleshootingResult> ExecuteTroubleshootingWorkflowAsync<T>(
        IQueryable<T> query, string queryName)
    {
        var result = new TroubleshootingResult
        {
            QueryName = queryName,
            StartTime = DateTime.UtcNow
        };

        try
        {
            // Step 1: Initial diagnosis
            _logger.LogInformation("Starting troubleshooting workflow for query {QueryName}", queryName);

            var diagnosis = await _diagnosticsService.DiagnoseQueryAsync(query, queryName);
            result.Diagnosis = diagnosis;

            // Step 2: Categorize issues
            var issueCategories = CategorizeIssues(diagnosis.Issues);
            result.IssueCategories = issueCategories;

            // Step 3: Prioritize fixes
            var prioritizedFixes = PrioritizeFixes(diagnosis.Recommendations);
            result.PrioritizedFixes = prioritizedFixes;

            // Step 4: Apply fixes
            foreach (var fix in prioritizedFixes.Take(3)) // Apply top 3 fixes
            {
                var fixResult = await ApplyFixAsync(query, fix);
                result.AppliedFixes.Add(fixResult);

                if (fixResult.Success)
                {
                    result.SuccessfulFixes++;
                }
            }

            // Step 5: Validate improvements
            result.ValidationResults = await ValidateImprovementsAsync(query, result.AppliedFixes);

            result.EndTime = DateTime.UtcNow;
            result.Success = result.SuccessfulFixes > 0;

            _logger.LogInformation("Troubleshooting workflow completed for {QueryName}. " +
                "Applied {FixCount} fixes successfully", queryName, result.SuccessfulFixes);

            return result;
        }
        catch (Exception ex)
        {
            result.Error = ex.Message;
            result.Success = false;
            _logger.LogError(ex, "Troubleshooting workflow failed for query {QueryName}", queryName);
            return result;
        }
    }

    private List<IssueCategory> CategorizeIssues(List<QueryIssue> issues)
    {
        return issues.GroupBy(i => i.Type)
            .Select(g => new IssueCategory
            {
                Type = g.Key,
                Issues = g.ToList(),
                Severity = g.Max(i => i.Severity),
                Count = g.Count()
            })
            .OrderByDescending(c => c.Severity)
            .ToList();
    }

    private List<Recommendation> PrioritizeFixes(List<Recommendation> recommendations)
    {
        return recommendations
            .OrderByDescending(r => r.Priority)
            .ThenBy(r => r.ImplementationEffort)
            .ThenByDescending(r => r.ExpectedImpact)
            .ToList();
    }

    private async Task<FixResult> ApplyFixAsync<T>(IQueryable<T> query, Recommendation fix)
    {
        var result = new FixResult
        {
            Recommendation = fix,
            AppliedAt = DateTime.UtcNow
        };

        try
        {
            switch (fix.Issue.Type)
            {
                case IssueType.NPlusOneQuery:
                    result = await FixNPlusOneQueryAsync(query, fix);
                    break;

                case IssueType.InefficientProjection:
                    result = await FixInefficientProjectionAsync(query, fix);
                    break;

                case IssueType.MissingIndex:
                    result = await FixMissingIndexAsync(fix);
                    break;

                default:
                    result.Success = false;
                    result.Error = $"No automated fix available for {fix.Issue.Type}";
                    break;
            }

            return result;
        }
        catch (Exception ex)
        {
            result.Success = false;
            result.Error = ex.Message;
            return result;
        }
    }

    private async Task<FixResult> FixNPlusOneQueryAsync<T>(IQueryable<T> query, Recommendation fix)
    {
        var result = new FixResult { Recommendation = fix };

        // Analyze the query to identify N+1 patterns
        var analysis = await _queryOptimizer.AnalyzeQueryAsync(query);

        if (analysis.HasNPlusOnePattern)
        {
            var optimizedQuery = await _queryOptimizer.OptimizeNPlusOneAsync(query);
            result.OptimizedQuery = optimizedQuery;
            result.Success = true;
            result.Description = "Applied Include() statements to prevent N+1 queries";
        }

        return result;
    }
}

public class TroubleshootingResult
{
    public string QueryName { get; set; }
    public DateTime StartTime { get; set; }
    public DateTime EndTime { get; set; }
    public DiagnosticReport Diagnosis { get; set; }
    public List<IssueCategory> IssueCategories { get; set; } = new();
    public List<Recommendation> PrioritizedFixes { get; set; } = new();
    public List<FixResult> AppliedFixes { get; set; } = new();
    public List<ValidationResult> ValidationResults { get; set; } = new();
    public int SuccessfulFixes { get; set; }
    public bool Success { get; set; }
    public string Error { get; set; }
}

Key Takeaways for Technical Lead Level:

1. Systematic Approach: Implement comprehensive profiling, alerting, and troubleshooting systems that work together
2. Proactive Monitoring: Use real-time monitoring to catch issues before they impact users
3. Automated Resolution: Build systems that can automatically detect and resolve common issues
4. Performance Metrics: Track multiple dimensions of performance (time, memory, CPU, I/O)
5. Root Cause Analysis: Implement tools to quickly identify the underlying causes of issues
6. Documentation: Maintain detailed documentation of common issues and their resolutions
7. Team Training: Ensure the team understands how to use these tools effectively

These implementations demonstrate enterprise-level thinking about LINQ performance management, showing you can design and implement comprehensive solutions that scale with the application's needs.

Official references

• Deferred execution
• EF Core efficient querying