Overview

This is a small collection of Entity Framework joins I have seen or written over the past couple of years. The alternative is to build stored procedures or views. But some shops prefer not to give developers this much access, therefore a quick simple work around is to build a join with Entity Framework. Stored Procedure will likely be faster in performance and easier to build/debug when joins become more complex.

Note: .ToList() at the end of an EF statement will format the data to JSON,

Inner Join with a select

var qryResults = from a in db.table1.Where(col => col.Category == CategoryId)
                                 .Where(col =>    col.title.Contains(searchString))
join b in db.table2 on a.DynoType equals b.Id
select new
{
   a.Id,
   a.column1,
   a.column2,
};

To use a Form Model to return your results – Not Necessary but here it is anyway.

var results = from m in qryResults
                select new FormModelName()
                {
                    Id = m.Id,
                    IsStock = m.IsStock,                                  
                    HP = m.HP,
                    MaxHPRPM = m.MaxHPRPM,
                    TQ = m.TQ,
                    MaxTQRPM = m.MaxTQRPM,
                    // RecordDate = m.DateRecorded
                };

Inner Join with Multiple Conditions

For multiple conditions, use new {}. SQL you could write a.col1 = b.col1 AND a.col2 = b.col2.
For Entity Framework the SQL Statement would translate to the following:
join b in db.Table_B on new { a.col1, a.col2} equals new { b.col1, b.col2 }

var results = (from a in db.Table_A
              join b in db.Table_B on new { a.Column1, a.Column2} equals new { b.Column1, b.Column2 } 
              where a.Column_4 == 1
              where a.Column_5 == 'A'
              select new {
                   b.Column1,
                   a.Column1,
                   a.Column2 
              }).ToList()

Grouping

Joining tables and grouping multiple columns to get a total count. In the example below, a “Contestants” and “VotingHistory” table are joined and grouped by Contestants ID and Name to get a total count of votes from Voting History.

var userData = (from m in db.Contestants
                join u in db.VotingHistory on m.ContestId equals u.ContestId
                where u.isActive == true 
                where m.ContestId == 2
                group m by new { m.ContestantId } into g
                select new { ContestId = g.Key.ContestantId, Title = g.Key.ContestantName, Count = g.Count() }
).ToList();

ContestId       Title               Count 
------------   -----------------   --------  
1032           "Contestant 1"         61  
1042           "Contestant 3"         43  
1099           "Contestant 4"         24  
1002           "Contestant 1"         12  

(4 row(s) affected)

Grouping multiple columns:

group g by new { m.COL1, m.COL2} into g

Selecting Specific Columns

var results = db.Events.Where(col => col.IsActive == true)
                       .Select(col => new { col.Id, 
                                            col.Title, 
                                            col.Description, 
                                            col.IsActive, 
                                            col.StartDate, 
                                            col.EndDate }).ToList();

Selecting Columns With Distinct Values

Adding Distinct() before ToList() will only bring back distinct values.

var results = db.Contestants.Where(col => col.IsActive == true)
                       .Select(col => new { col.Event }).Distinct().ToList();

Ordering

Following example will return a list of records ordered from newest to the oldest.

var results = db.Events.Where(col => col.IsActive == true).OrderByDescending(col => col.ID)

Or sort from oldest to newest.

var results = db.Events.Where(col => col.IsActive == true).OrderByDescending(col => col.ID)

Selecting Specific Columns

var results = db.Events.Where(col => col.IsActive == true)
                       .Select(col => new { col.Id, 
                                            col.Title, 
                                            col.Description, 
                                            col.IsActive, 
                                            col.StartDate, 
                                            col.EndDate }).ToList();

Descending order in a Join Example

Using orderby a.COLUMN_NAME descending OR ascending to sort the result set

var results = db.Events.Where(col => col.IsActive == true)
                        orderby a.StartDate descending
                       .Select(col => new { col.Id, 
                                            col.Title, 
                                            col.Description, 
                                            col.IsActive, 
                                            col.StartDate, 
                                            col.EndDate }).ToList();

Order by DateTime Hour or Minute Only

Using orderby a.COLUMN_NAME.Hour descending OR ascending to sort the result set by the hour. Also can use Minutes or Seconds depending on the application. This can come in handy if the data is for the current date.

var currentDateTime = DateTime.Today;

var results = from a in db.Events 
              where a.StartDate >= currentDateTime              
              select new { col.Id, 
                           col.Title, 
                           col.Description, 
                           col.IsActive, 
                           col.StartDate, 
                           col.EndDate })
.OrderBy(c => c.StartDate.Hour).ToList();

Aliases

Example of an alias when joining multiple tables that have duplicate column name. In this case there is a “RequestService” and an “OfferServices” table joined by a “OfferRequestMatch”.

var results = from a in db.Matches
                 join b in db.Offers on a.OfferId equals b.Id
                 join c in db.Requests on a.RequestId equals c.Id
                 where a.IsConnected == true
                 select new
                 {
                     a.Id,
                     a.DateConnected,                     
                     OfferStatus = a.Status,
                     OfferDetails = b.Details,
                     RequestStatus = b.Status,
                     RequestDetails = c.Details
                 };

Exclude From List in Where Clause

Get a list of Offer Ids from Matches and then exclude them in the Where clause.

var exludeList = db.ClosedAccounts.Where(col => col.Status < 3).Select(col => col.OfferId).ToArray();

var results = db.Offers.Where(col => !excludeList.Contains(col.Id))
                       .OrderByDescending(col => col.DateCreated).ToList();

Transactions

Transactions allows you to guarantee a sequence of SQL statements are executed before committing the changes. For example, an API action that executes an insert before an update on another table otherwise do not commit the changes.

The transaction can be initiated with Database.BeginTransaction() and Commit() to commit the changes.

The database entity is instantiated with

DatabaseEntities db = new DatabaseEntities()

Using the database object we can declare a variable to begin and commit the transaction.

var dbContextTransaction = db.Database.BeginTransaction()

Example: A method to add a new record in the Status History table and then update another table labeled Support.

public object AddStatus(StatusHistoryModel form)
{
    try
    {
        var currentDateTime = DateTime.Now;

        using (DatabaseEntities db = new DatabaseEntities())
        {

            using (var dbContextTransaction = db.Database.BeginTransaction())
            {
                StatusHistory newRecord = new StatusHistory();
                newRecord.Status = form.Status;
                newRecord.Notes = form.Notes;
                newRecord.DateCreated = currentDateTime;
                newRecord.RecordedBy = form.UserId;

                db.StatusHistories.Add(newRecord);


                Support supportRecord = db.Supports.Find(form.RecordId);
                supportRecord.Status = form.Status;
                supportRecord.DateApproved = currentDateTime;                

                db.SaveChanges();

                dbContextTransaction.Commit();
            }

            return new { msg = "Success" };

        }
    }
    catch (Exception e)
    {
        return new { msg = e.InnerException };
    }

}

Note: The db.SaveChanges() will not execute if the insert or update fails.