LINQ design interview question

LINQ is one of my favorite interview topics for many reasons. It allows me to check a candidate's ability to define logic, use SQL skills, showcase lambda expressions, distill the problem into easy steps and of course, see some code in action. Also, since LINQ code is generally quite compact, it is perfect for white boarding.

I was recently helping a friend solve a problem and felt that it could be modified to an excellent interview question. It has a bit of algorithm design, heavy use of simple to medium complex LINQ and is not more that 10-15 lines of actual code. Perfect.

Question: Given a class of students  (ID, Name, Grades) with results for at least 2 semesters, find the students whose Grades have significantly improved in the second semester as compared to the first. Bonus: List out top 25% of the students.

As the first step, let us define the rough algorithm for solving this problem:

1. Load Student data for first semester
2. Load student data for second semester
3. Merge Grades for each student from second semester into the first semester result set
4. For each student, calculate the grade difference for students whose grades have improved (you do not want to waste computational cycles for students whose grades have fallen)
5. For each student from 4 above, calculate the grade improvement percent
6. Sort the result by descending order of grade improvement percent
7. Select rows that constitute top 25% of students

Now, let’s define our POCO class that will represent the student data structure. The data structure has two parts: basic student properties (Id, Name, Grade) and some computational properties that we will use to solve our algorithm.

    public class StudentRecord
    {
        // basic properties
        public int Id { get; set; }
        public string Name { get; set; }
        public double Grade { get; set; }

        // computational properties
        public double GradeSecondSemester { get; set; }
        public double GradeDifference { get; set; }
        public double GradeDifferencePercent { get; set; }
    }

In addition to a Student record, we also need to a collection to hold these records. Let’s call it ClassReport:

    public class ClassReport
    {
        // some basic properties of a class
        public int Id { get; set; }
        public string Name { get; set; }
        public int SemesterId { get; set; }

        // list of student records for that semester
        public List<StudentRecord> Students;

        public ClassReport()
        {
            Students = new List<StudentRecord>();
        }
    }

For steps 1 and 2 of the algorithm, I am not going to focus on this blog. We will assume that we have a way to load this data (stubbed in our code sample below).

Step 1 and 2: Load Student data:

We will mock up our student data in this example. In real life, this data probably will come from a database or a web service call, etc. As you can see below, we generate some dummy student data for two different semesters. Note that the student Id’s need to match across (not really a hard requirement, but good to have for our demo purposes).

        static void Main(string[] args)
        {
            // Step 1 and 2: Load Student data
            ClassReport cr1 = MockSem1();
            ClassReport cr2 = MockSem2();

        }

        private static ClassReport MockSem1()
        {
            ClassReport cr = new ClassReport()
            {
                Id = 1,
                Name = "First Grade",
                SemesterId = 1
            };

            // add dummy data - in real life, this comes from database
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 1,
                    Name = "Ray",
                    Grade = 90
                });
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 2,
                    Name = "Jack",
                    Grade = 80
                });
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 3,
                    Name = "John",
                    Grade = 60
                });
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 4,
                    Name = "Lisa",
                    Grade = 67
                });
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 5,
                    Name = "Jill",
                    Grade = 94
                });
            return cr;
        }

        private static ClassReport MockSem2()
        {
            ClassReport cr = new ClassReport()
            {
                Id = 1,
                Name = "First Grade",
                SemesterId = 2
            };

            // add dummy data - in real life, this comes from database
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 1,
                    Name = "Ray",
                    Grade = 85
                });
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 2,
                    Name = "Jack",
                    Grade = 95
                });
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 3,
                    Name = "John",
                    Grade = 82
                });
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 4,
                    Name = "Lisa",
                    Grade = 95
                });
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 5,
                    Name = "Jill",
                    Grade = 96
                });
            return cr;
        }

Step 3: Merge Grades for each student from second semester into the first semester result set

Here is where our LINQ fun starts. In one of my previous post, I had explained how LINQ join works. Now would be a good time to review bunch of LINQ related interview questions and answers from the past.

            // step 3: Merge Grades for each student from second 
            // semester into the first semester result set

            // in-line merge; no need for a separate assignment
            // for each student in first semester report
            (from firstSem in cr1.Students
             // join to second sem class report
             join secondSem in cr2.Students 
                // on student id as the key
                on firstSem.Id equals secondSem.Id
             // assign second sem grade to first (via a select; clever)
             select firstSem.GradeSecondSemester = secondSem.Grade)
             // execute 
             .ToList();

As you can see from the LINQ statement, we join first semester class report with second semester and then do an in-line update of the first semester data structure’s GradeSecondSemester property.

Step 4: Calculate grade difference for students whose grades have improved

Now we need to process those students whose grades improved in the 2nd semester and update our data structure to reflect the difference:

// step 4: For each student, calculate the grade difference 
// for students whose grades have improved (you do not want 
// to waste computational cycles for students whose grades have fallen)
cr1.Students
    // find all students who did better in 2nd semester
    .FindAll(s => ((s.GradeSecondSemester - s.Grade) > 0))
    // and update their grade difference property
    .ForEach(x => x.GradeDifference = (x.GradeSecondSemester - x.Grade));

We use FindAll to find those students who have better grades and then loop for those records using ForEach to update the GradeDifference property.

Step 5: For each student from 4 above, calculate the grade improvement percent

// Step 5: For each student from 4 above, 
// calculate the grade improvement percent

// 5.a. calculate total difference sum
double gradeDiffSum  = cr1.Students.Sum(x => x.GradeDifference);

// 5.b. calculate percent for each row
cr1.Students
    // find all students who did better in 2nd semester
    .FindAll(s => ((s.GradeSecondSemester - s.Grade) > 0))
    // update grade percent
    .ForEach(x => x.GradeDifferencePercent = 
        ((x.GradeDifference * 100) / gradeDiffSum));

Step 6: Sort the result by descending order of grade improvement percent

By this point, I am hoping you get the gist of how to approach such LINQ queries. Sorting a list is a relatively simple operation as shown below:

    // Step 6: Sort the result by descending order of grade improvement percent
    var sortedList = 
        (from entry in cr1.Students
            orderby entry.GradeDifferencePercent descending
            select entry)
        .ToList();

Step 7: Select rows that constitute top 25% of students

We use TakeWhile here to keep selecting students till we reach our number.

    //Step 7: Select rows that constitute top 25% of students
    // 7.a. - sum of grade diff percent
    double gradeDiffPercentSum = 0;

    // 7.b - take as many rows as needed till we reach the 25% sum
    var bestStudents = 
        sortedList
        .TakeWhile(x => 
            ((gradeDiffPercentSum += x.GradeDifferencePercent) <= 25.0))
        .ToList();

    // 7.c account for the case where the first element is more than 25
    if (bestStudents.Count() == 0)
        bestStudents = sortedList.Take(1).ToList();

That’s it. You now have a list of top 25% students whose grades have improved in the second semester.

I am pasting the complete program here in case you like to see the whole thing in one shot.

The complete solution:

using System.Linq;

namespace StudentReport
{
 class Program
 {
    static void Main(string[] args)
    {
        // Step 1 and 2: Load Student data
        ClassReport cr1 = MockSem1();
        ClassReport cr2 = MockSem2();

        // step 3: Merge Grades for each student from second 
        // semester into the first semester result set

        // in-line merge; no need for a separate assignment
        // for each student in first sem report
        (from firstSem in cr1.Students
        // join to second sem class report
        join secondSem in cr2.Students
            // on student id as the key
            on firstSem.Id equals secondSem.Id
        // assign second sem grade to first (via a select; clever)
        select firstSem.GradeSecondSemester = secondSem.Grade)
            // execute 
        .ToList();

        // step 4: For each student, calculate the grade difference 
        // for students whose grades have improved (you do not want 
        // to waste computational cycles for students whose grades have fallen)
        cr1.Students
            // find all students who did better in 2nd semester
            .FindAll(s => ((s.GradeSecondSemester - s.Grade) > 0))
            // and update their grade difference property
            .ForEach(x => x.GradeDifference = (x.GradeSecondSemester - x.Grade));

        // Step 5: For each student from 4 above, 
        // calculate the grade improvement percent

        // 5.a. calculate total difference sum
        double gradeDiffSum = cr1.Students.Sum(x => x.GradeDifference);

        // 5.b. calculate percent for each row
        cr1.Students
            // find all students who did better in 2nd semester
            .FindAll(s => ((s.GradeSecondSemester - s.Grade) > 0))
            // update grade percent
            .ForEach(x => x.GradeDifferencePercent =
                ((x.GradeDifference * 100) / gradeDiffSum));


        // Step 6: Sort the result by descending order of grade improvement percent
        var sortedList =
            (from entry in cr1.Students
            orderby entry.GradeDifferencePercent descending
            select entry)
            .ToList();


        //Step 7: Select rows that constitute top 25% of students
        // 7.a. - sum of grade diff percent
        double gradeDiffPercentSum = 0;

        // 7.b - take as many rows as needed till we reach the 25% sum
        var bestStudents =
            sortedList
            .TakeWhile(x =>
                ((gradeDiffPercentSum += x.GradeDifferencePercent) <= 25.0))
            .ToList();

        // 7.c account for the case where the first element is more than 25
        if (bestStudents.Count() == 0)
            bestStudents = sortedList.Take(1).ToList();

        // do something with this result set
    }

        private static ClassReport MockSem1()
        {
            ClassReport cr = new ClassReport()
            {
                Id = 1,
                Name = "First Grade",
                SemesterId = 1
            };

            // add dummy data - in real life, this comes from database
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 1,
                    Name = "Ray",
                    Grade = 90
                });
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 2,
                    Name = "Jack",
                    Grade = 80
                });
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 3,
                    Name = "John",
                    Grade = 60
                });
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 4,
                    Name = "Lisa",
                    Grade = 67
                });
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 5,
                    Name = "Jill",
                    Grade = 94
                });
            return cr;
        }

        private static ClassReport MockSem2()
        {
            ClassReport cr = new ClassReport()
            {
                Id = 1,
                Name = "First Grade",
                SemesterId = 2
            };

            // add dummy data - in real life, this comes from database
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 1,
                    Name = "Ray",
                    Grade = 85
                });
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 2,
                    Name = "Jack",
                    Grade = 85
                });
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 3,
                    Name = "John",
                    Grade = 70
                });
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 4,
                    Name = "Lisa",
                    Grade = 82
                });
            cr.Students.Add(
                new StudentRecord()
                {
                    Id = 5,
                    Name = "Jill",
                    Grade = 96
                });
            return cr;
        }
    }
}

LINQ interview questions part 3

In this post, we will do a quick review of LINQ operators Any() and All().

Question 1: Using LINQ, determine if any word in a list contains the substring “ei”.

    string[] words = { "believe", "relief", "receipt", "field" };

    bool iAfterE = words.Any(w => w.Contains("ei"));

    Console.WriteLine("list has words that contains 'ei': {0}", iAfterE);

Question 2: Using LINQ, return a grouped list of products only for categories that have at least one product that is out of stock.

In this case, we run any over a grouping as shown below:

    List<Product> products = GetProductList();

    var productGroups =

        from p in products

        group p by p.Category into g

        where g.Any(p => p.UnitsInStock == 0)

        select new { Category = g.Key, Products = g };

Question 3: Determine if an array of numbers contains all odds.

int[] numbers = { 1, 11, 3, 19, 41, 65, 19 };

bool onlyOdd = numbers.All(n => n % 2 == 1);

Console.WriteLine("The list contains only odd numbers: {0}", onlyOdd);

Question 4: Using LINQ, return a grouped list of products only for categories that have all of their products in stock.

    List<Product> products = GetProductList();

    var productGroups =

        from p in products

        group p by p.Category into g

        where g.All(p => p.UnitsInStock > 0)

        select new { Category = g.Key, Products = g };

LINQ interview questions part 2

Based on popular demand, I am posting a follow up post to my first LINQ interview questions article. In this post, we will follow a similar pattern of rapid fire interview questions and answers on LINQ concepts.

Question 1: How can you sort a result set based on 2 columns?

Assume that you have 2 tables – Product and Category and you want to sort first by category and then by product name.

var sortedProds = _db.Products.Orderby(c => c.Category).ThenBy(n => n.Name) 

Question 2: How can you use LINQ to query against a DataTable?

You cannot query against the DataTable's Rows collection, since DataRowCollection doesn't implement IEnumerable<T>. You need to use the AsEnumerable() extension for DataTable. As an example:

var results = from myRow in myDataTable.AsEnumerable() 
where myRow.Field<int>("RowNo") == 1 
select myRow; 

Question 3: LINQ equivalent of foreach for IEnumerable<T>

There is no ForEach extension for IEnumerable; only for List. There is a very good reason for this as explained by Eric Lippert here.

Having said that, there are two ways you can solve this:

Convert the items to list and then do a foreach on it:

items.ToList().ForEach(i => i.DoStuff());

Or, alternatively, you can write an extension method of your own.

public static void ForEach<T>(this IEnumerable<T> enumeration, Action<T> action) 
{ 
    foreach(T item in enumeration) 
    { 
        action(item); 
    } 
}

Question 4: When to use .First and when to use .FirstOrDefault with LINQ?

You should use First when you know or expect the sequence to have at least one element. In other words, when it is an exceptional if the sequence is empty.

Use FirstOrDefault, when you know that you will need to check whether there was an element or not. In other words, when it is legal for the sequence to be empty. You should not rely on exception handling for the check.

Question 5: Write a LINQ expression to concatenate a List<string> in a single string separated by a delimiter.

First of all, it is better to use string.Join to tackle this problem. But for interview purposes, this problem can be approached as follows:

string delimeter = ","; 

List<string> items = new List<string>() { "foo", "boo", "john", "doe" }; 

Console.WriteLine(items.Aggregate((i, j) => i + delimeter + j)); 

Question 6: Write a LINQ expression to concatenate a List<MyClass> objects in a single string separated by a delimiter. The class provides a specific property (say Name) that contains the string in question.

items.Select(i => i.Name).Aggregate((i, j) => i + delimeter + j)

Entity Framework interview questions

Entity Framework is Microsoft’s latest and recommended way for data access for new applications. EF 4.1 enhances the offering by adding support for Code First model and DbContext API’s. The post assumes basic knowledge of Entity Framework. If you are not familiar with EF, I would recommend that you review the basics @ http://msdn.microsoft.com/en-us/data/ef. If you are interested in Code First approach, a very good tutorial that I would recommend is http://blogs.msdn.com/b/adonet/archive/2011/03/15/ef-4-1-code-first-walkthrough.aspx.

In this post we will explore 10 interview questions on Code First Data Annotations.

Question 1: Design a code first data model which has a Project class that can contain a bunch of tasks.

For our discussion, we will assume that we are using the Code First model and that our model is made up of the following 2 classes:

using System;
using System.Collections.Generic;

namespace EFDemo.Model
{
    // Code first relies on a programming pattern 
    // referred to as convention over configuration. 
    // What this means is that if you want to use code first, 
    // your classes need to follow the certain conventions 
    // while defining the schema. 
    // This allows EF to infer the schema that it needs to 
    // create to get the job done.
    public class Project
    {
        // Code First infers this as the primary key column
        public int Id { get; set; }
        // this becomes a nullable column
        public string Name { get; set; }
        public string Description { get; set; }

        // list of tasks for a project
        public virtual List<Task> Tasks { get; set; }
    }

    public class Task
    {
        // Code First infers this as the primary key column
        public int TaskId { get; set; }
        public string Name { get; set; }

        public DateTime StartDate { get; set; }
        public DateTime EndDate { get; set; }

        // this is inferred as Foreign key to project table
        public int ProjectId { get; set; }
    }
     
}

This model produces the following database. I have highlighted the relevant items that I would like you to understand before we proceed further.

Now let’s review a few simple entity framework interview questions.

Question 2: Using Code First model, how can I mark a field/property as the primary key if it does not follow the code first convention?

In our case above, EF looks for the word “ID” with a combination with the entity name (e.g. Project) to determine both the EntityKey and the primary key. If we rename the “Id” to say “UniqueProjectIdentifier”, we will need to decorate that property with the KeyAttribute ([Key]) to make it all work.

In the code below, we redefined our primary key but did not provide any data annotations.

public class Project
{
    // Code First has to be told that 
    // this as the primary key column
    public int UniqueProjectIdentifier { get; set; }
    // this becomes a nullable column
    public string Name { get; set; }
    public string Description { get; set; }

    // list of tasks for a project
    public virtual List<Task> Tasks { get; set; }
}

This produces the following error:

The fix is simple. Just add the [Key] attribute as shown below.

 

public class Project
{
    // Code First has to be told that 
    // this as the primary key column
    [Key]
    public int UniqueProjectIdentifier { get; set; }
    // this becomes a nullable column
    public string Name { get; set; }
    public string Description { get; set; }

    // list of tasks for a project
    public virtual List<Task> Tasks { get; set; }
}

Question 3: When you have a annotate a property as Primary key in a table, how do you enable foreign key relationship from another table?

Although this “fix” solved the primary key issue for the Project class, it failed to infer our Foreign Key relationship in the Task class. It actually created a new FK and ignored our ProjectId key.

Now that we have a custom primary key, we also have to annotate a foreign key for the Task table. The solution is to define a navigation property for Task and annotate it to mark the ProjectId property as the FK.

public class Task
{
    // Code First infers this as the primary key column
    public int TaskId { get; set; }
    public string Name { get; set; }

    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }

    // this is inferred as Foreign key to project table
    public int ProjectId { get; set; }

    // explicitly define the FK
    [ForeignKey("ProjectId")]
    public virtual Project Project { get; set; }
}

Question 4: How do you mark a property as required? For example, For a Project, the Name is a required field.

You use the [Required] attribute to mark a property as required.

public class Project
{
    // Code First has to be told that 
    // this as the primary key column
    [Key]
    public int UniqueProjectIdentifier { get; set; }
    // this becomes a non-nullable column
    [Required]
    public string Name { get; set; }
    public string Description { get; set; }

    // list of tasks for a project
    public virtual List<Task> Tasks { get; set; }
}

Question 5: How do you enforce a field to have a minimum and maximum number of characters? For example, the Description on a Project should be a minimu of 10 and a maximum of 500?

EF provides us with convenient property annotations of MinLength and maxLength.

public class Project
{
    // Code First has to be told that 
    // this as the primary key column
    [Key]
    public int UniqueProjectIdentifier { get; set; }
    // this becomes a non-nullable column
    [Required]
    public string Name { get; set; }
    [MaxLength(500, ErrorMessage="Maximum of 500 characters please")]
    [MinLength(10, ErrorMessage="Minimum of 10 characters required")]
    public string Description { get; set; }

    // list of tasks for a project
    public virtual List<Task> Tasks { get; set; }
}

After the 2 changes described above, our database looks like:

Question 6: Define a property in project class named ProjectCode that is not mapped to the database. ProjectCode is internally calculated as a combination of project ID and Title.

Normally, in code first convention, all properties are mapped to the database. If we want to exclude a specific property (generally a computed property), we can annotate it with [NotMapped] attribute.

public class Project
{
    // Code First has to be told that 
    // this as the primary key column
    [Key]
    public int UniqueProjectIdentifier { get; set; }
    // this becomes a non-nullable column
    [Required]
    public string Name { get; set; }
    [MaxLength(500, ErrorMessage="Maximum of 500 characters please")]
    [MinLength(10, ErrorMessage="Minimum of 10 characters required")]
    public string Description { get; set; }

    // list of tasks for a project
    public virtual List<Task> Tasks { get; set; }

    [NotMapped]
    public string ProjectCode
    {
        get
        {
            return UniqueProjectIdentifier + Name;
        }
    }
}

Question 7: If your domain entities are defined using a set of classes, how can you combine them in EF to form one complete entity?

Let us assume that our Project class has another class called ProjectDetails which has date created and the description field. Using normal EF code first data model, EF will create 3 tables. But we want to tell EF to create only 2 tables (Project and task). To achieve this we will use the [ComplexType] annotation on the Project Details as shown below:

public class Project
{
    // Code First has to be told that 
    // this as the primary key column
    [Key]
    public int UniqueProjectIdentifier { get; set; }
    // this becomes a non-nullable column
    [Required]
    public string Name { get; set; }
    // references the complex type as part of the 
    // project object in the database
    public ProjectDetails Details { get; set; }
    // list of tasks for a project
    public virtual List<Task> Tasks { get; set; }

    [NotMapped]
    public string ProjectCode
    {
        get { return UniqueProjectIdentifier + Name;}
    }
}

[ComplexType]
public class ProjectDetails
{
    public DateTime? DateCreated { get; set; }
    [MaxLength(500, ErrorMessage = "Maximum of 500 characters please")]
    [MinLength(10, ErrorMessage = "Minimum of 10 characters required")]
    public string Description { get; set; }
}

This results in the following database schema:

The calling code also needs to be changed:

class Program
{
    static void Main(string[] args)
    {

        Database.SetInitializer<EFDemoContext>(new DropCreateDatabaseIfModelChanges<EFDemoContext>());

        using (var ctx = new EFDemoContext())
        {
            Project p = new Project()
            {
                Name = "Project 1",
                Details = new ProjectDetails()
                {
                    DateCreated = DateTime.Now,
                    Description = "some project description"
                }
            };

            ctx.Projects.Add(p);
            ctx.SaveChanges();
        }
    }
}

Question 8: How can you tell EF to have a different table or column name than that defined for the class?

By convention, EF defines the table and column names based on your class and property names. You can use the [Table] and [Column] annotations to tell EF to use different names.

[Table("ProjectItems")]
public class Task
{
    // Code First infers this as the primary key column
    public int TaskId { get; set; }
    public string Name { get; set; }

    [Column("CreationDate")]
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }

    // this is inferred as Foreign key to project table
    public int ProjectId { get; set; }

    // explicitly define the FK
    [ForeignKey("ProjectId")]
    public virtual Project Project { get; set; }
}

This causes EF to create a data model where the Tasks table is represented by Projectitems and the StartDate column to be named as CreationDate.

Question 9: For a datetime property, how can you tell EF to automatically compute and insert the current date time when the row is created?

In our Project tasks, we want to automatically set the creation date when a new row is inserted. We can achieve this by telling EF that this property is a [DatabaseGenerated] property and that it is computed.

[Table("ProjectItems")]
public class Task
{
    // Code First infers this as the primary key column
    public int TaskId { get; set; }
    public string Name { get; set; }

    [Column("CreationDate")]
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }

    // this is inferred as Foreign key to project table
    public int ProjectId { get; set; }

    // explicitly define the FK
    [ForeignKey("ProjectId")]
    public virtual Project Project { get; set; }
}

This code will throw an exception. Why? Because code first won't be able to determine the formula for the computed column. To solve this, you should only use this when pointing to existing databases OR use the [TimeStamp] column.

Another use of this attribute is when you do NOT want your primary key to be an auto incremented.

Question 10: When two tables have multiple relationships (for example, a task is created by employee 1 and updated by employee 2), who do you indicate which relationships go with which property?

Entity Framework provides us with [InverseProperty] attribute to indicate multiple relationships between two tables. Consider the following code first model where the Task class now has 2 pointers to Employee for CreatedBy and UpdatedBy. Also we have added an Employee class which has a list of tasks created and updated. NOTE that we have not (yet) added any data annotations to signify any inverse relationships. The goal is to show you how EF will not be able to recognize this.

[Table("ProjectItems")]
public class Task
{
    // Code First infers this as the primary key column
    public int TaskId { get; set; }
    public string Name { get; set; }

    [Column("CreationDate")]
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }

    // this is inferred as Foreign key to project table
    public int ProjectId { get; set; }

    // explicitly define the FK
    [ForeignKey("ProjectId")]
    public virtual Project Project { get; set; }

    public Employee CreatedBy { get; set; }
    public Employee UpdatedBy { get; set; }
}

public class Employee
{
    public int Id { get; set; }
    public string name { get; set; }

    public List<Task> TasksCreated { get; set; }
    public List<Task> TasksUpdated { get; set; }
}

The database generated on running this model is shown below:

As you can see above, the Tasks (ProjectItems) table is not what you really expected. Let’s fix this by using the [Inverseproprty] attribute.

public class Employee
{
    public int Id { get; set; }
    public string name { get; set; }

    [InverseProperty("CreatedBy")]
    public List<Task> TasksCreated { get; set; }
    [InverseProperty("UpdatedBy")]
    public List<Task> TasksUpdated { get; set; }
}

Now that we let EF now the relationships, it is able to figure it out and generates the following database for us:

I hope that post has been helpful to you to understand a few of the data annotations provided by Entity framework.

LINQ JOIN interview questions

The JOIN clause is one of the most complex and potentially confusing aspects of LINQ. A thorough understanding of the JOIN clause can help you glide easily through the interview process. Most of the interview questions that I see around JOIN using LINQ are both direct (and simple) JOIN queries and in a few cases in-direct applications of the JOIN clause. If the position requires extensive SQL or LINQ expertise, you probably would already be familiar with complex joins.

Well, so what does a JOIN clause do? The join clause is useful for associating elements from different source sequences that have no direct relationship in the object model. The only requirement is that the elements in each source share some value that can be compared for equality. For example, a food distributor might have a list of suppliers of a certain product, and a list of buyers. A join clause can be used, for example, to create a list of the suppliers and buyers of that product who are all in the same specified region. Let’s explore this notion with a few examples.

Question: For all the categories in the database, list the products in those categories. In addition, the output should just include the product and category names.

This is a simple JOIN between the products and the categories.

var innerGroupJoinQuery =
    from category in categories // for all categories
    // join with product table on product category ID
    join prod in products on category.ID equals prod.CategoryID into prodGroup
    // create a new object with just the product and category names
    select new { CategoryName = category.Name, Products = prodGroup };

Note that in this code example, if an element from categories has no matching products, that category will not appear in the results. We will review how to fix that using LEFT OUTER JOIN below.

Question: For all the categories in the database, list the products in those categories. If a category does not have any products, still list the category.

In a left outer join, all the elements in the left source sequence are returned, even if no matching elements are in the right sequence. To perform a left outer join in LINQ, use the DefaultIfEmpty method in combination with a group join to specify a default right-side element to produce if a left-side element has no matches.

var leftOuterJoinQuery =
    from category in categories
    join prod in products on category.ID equals prod.CategoryID into prodGroup
    from item in prodGroup.DefaultIfEmpty(new Product{Name = String.Empty, CategoryID = 0})
        select new { CatName = category.Name, ProdName = item.Name };

Question: Given simple data structures of Person (Name) and Pet (Name, Owner), write a LINQ query that lists all the pets that each person owns.

This is a more complex example involving GROUP JOINS. The group join is useful for producing hierarchical data structures. It pairs each element from the first collection with a set of correlated elements from the second collection. GroupJoin has no direct equivalent in traditional relational database terms. However, this method does implement a superset of inner joins and left outer joins. Both of these operations can be written in terms of a grouped join.

So, our basic data structures can be represented as:

class Person
{
    public string Name { get; set; }
}
    
class Pet
{
    public string Name { get; set; }
    public Person Owner { get; set; }
}

And some sample data might look like this

Person magnus = new Person { Name = "Hedlund, Magnus" };
Person terry = new Person { Name = "Adams, Terry" };
Person charlotte = new Person { Name = "Weiss, Charlotte" };

Pet barley = new Pet { Name = "Barley", Owner = terry };
Pet boots = new Pet { Name = "Boots", Owner = terry };
Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte };
Pet daisy = new Pet { Name = "Daisy", Owner = magnus };

List<Person> people = new List<Person> { magnus, terry, charlotte };
List<Pet> pets = new List<Pet> { barley, boots, whiskers, daisy };

Now we have two ways to implement the same LINQ query. Using the traditional syntax:

// Create a list where each element is an anonymous type
// that contains the person's first name and a collection of 
// pets that are owned by them.
var query = from person in people
            join pet in pets on person equals pet.Owner into gj
            select new { OwnerName = person.Name, Pets = gj };

Using the Lambda syntax:

// Create a list where each element is an anonymous 
// type that contains a person's name and 
// a collection of names of the pets they own.
var query =
    people.GroupJoin(pets,
           person => person,
           pet => pet.Owner,
           (person, petCollection) =>
               new
               {
                   OwnerName = person.Name,
                   Pets = petCollection.Select(pet => pet.Name)
               });

Now for debugging purposes, we can spew out the info as:

foreach (var v in query)
{
    // Output the owner's name.
    Console.WriteLine("{0}:", v.OwnerName);
    // Output each of the owner's pet's names.
    foreach (Pet pet in v.Pets)
        Console.WriteLine("  {0}", pet.Name);
}

This should produce an output similar to the following.

/*
Hedlund, Magnus:
  Daisy
Adams, Terry:
  Barley
  Boots
Weiss, Charlotte:
  Whiskers
*/