LINQ - Left join with where conditions

-
Left joins don't come natural to LINQ but the solution is not that complicated.

Below is my example

I needed to pass in 3 fields for my where clause and I was getting back the LoginName field from the USERS table - unfortunately sometimes this field was empty and my join would not work - in the SQL we know and love this is easily accomplished with a left join (take all the fields from the first table or the table on the left and any that match in the second table)

Below you can see how this works. The 'subgroup' referenced below is just an alias, there is no table named subroup - almost acting like a temptable.

 var mygroupitems = (from e in MyEntities.Terr_History
                            join p in MyEntities.Users on e.Approver_User_Id  equals p.Users_Id into XX
                            from subgroup in XX.DefaultIfEmpty()
                            where e.tdg_PID == pid  && e.Account_Code==accountcode
                            && e.Status_=="Active"

                            select new Management.Data.DTO.Terr_HistoryDTO
                            {
                                Territory_Type=e.Territory_Type,
                                Rn_Descriptor = e.Rn_Descriptor,
                               Approver_User_Id=e.Approver_User_Id,
                              Approver_UserName = (subgroup==null? string.Empty:subgroup.Login_Name),
.... and so on

Comments

Post a Comment

Popular posts from this blog

Linq Exclude from separate list

-
 var urlList = new List<DisplayAssets>();    var assetsToExclude = (from a in db.DisplayAssets                 join h in db.Hardware_Assets on a.id equals h.displayId                 where h.assetId == hardwareid                 select a.id); var allAssets = db.DisplayAssets; var urls = allAssets.Where(x => !assetsToExclude.Contains(x.id));         urlList = urls.Select(a => new DisplayAssets()         {             locaterAddress  = a.locaterAddress,             assetType = a.assetType,             id = a.id         })         .ToList();
Read more

Sorting Ascending and Descending

-
Toggling the direction of your datagrid sort is easy using a session to remember which direction you sorted last.   You will want to declare an empty string in the top of your page       Dim SortField As String Then on your sort command add this logic - Example of entire command Private Sub dgTaskExceptions_SortCommand( ByVal source As Object , ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles dgTaskExceptions.SortCommand     SortField = e.SortExpression If Session( "toggle" ) = True Then SortField += " DESC" Session( "toggle" ) = False Else SortField += " Asc" Session( "toggle" ) = True End If Dim orderstring As String = SortField 'REBIND dgTaskExceptions.DataSource = POTracking_Data.tracking.getSampleTasks(orderstring) dgTaskExceptions.DataBind() End Sub   In my datalayer I add this logic to my query and pass in an OPTIONAL value Dim orderbyString As String = String .Empt
Read more

Linq Query Syntax vs Method Syntax

-
Referenced from MSDN: https://msdn.microsoft.com/en-us/library/bb397947.aspx Query syntax: IEnumerable<int> numQuery1 =               from num in numbers             where num % 2 == 0             orderby num             select num; Method syntax:         IEnumerable<int> numQuery2 = numbers.Where(num => num % 2 == 0).OrderBy(n => n)
Read more