A real world query with where's and ifs and nulls and multiple joins oh my!

-
I was looking for a good example of a Join but the examples I found were simple and did not include the other elements such as the where statement and multiple tables. So here is an example that works that includes those elements. I have eliminated some of the fields if you are wondering why I have so many joins that I didn't appear to use.

The following query has multiple joins, one of the joins is joining on two columns with a defaultifempty applied - essentially making it a left join. One of the fields is an integer but I wanted to switch out a string instead - hopefully this will help someone who is looking for the answer for one of these linq constructors.


MyEntities tfs = new MyEntities();


var rptCon = (from e in tfs.Company_Zips
join b in tfs.Franchisees on e.AccountCode equals b.AccountCode
join c in tfs.Account_StoreExtensions
on new{ e.Zip_StoreExtensionID,e.Accountcode} equals new{ c.StoreExtensionID,c.AccountCode} into ex
join p in tfs.Postal_Code_Territory on e.Postal_Code_Territory_Id equals p.Postal_Code_Territory_Id
where b.ConceptCode == ConceptCode
from subq in ex.DefaultIfEmpty()

select new DTO_Franchisee_Zip
{
Zip = p.Postal_Code,
Territory_Type = (e.TerritoryType == 0 ? "TFS" :
e.TerritoryType == 1 ? "Purchased" :
e.TerritoryType == 3 ? "Option" :
e.TerritoryType == 4 ? "Vendor" : "Error")

,Zip_StoreExtension = e.Zip_StoreExtension
,StoreExtension_URL= subq==null? String.Empty : subq.ExtensionURL

}).ToList();

return rptCon;

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