Need to append customer details with Site Purpose Type equal to ONE using FirstOrDefault()

Question

    public async Task<CustomerDropdownDTO> billToSalesInvoiceForCustomerAutoComplete([FromQuery][Required] long customerSeq)
    {
        IQueryable<CustomerDropdownDTO> query = from a in _dataContext.CustomerTable
                                                join CustomerAc in _dataContext.CustomerAccountTable on a.customerSeq equals CustomerAc.customerSeq into CustomerAcR
                                                from CustomerAcResult in CustomerAcR.DefaultIfEmpty()
                                                join CustomerAcSite in _dataContext.CustomerAcSiteTable on a.customerSeq equals CustomerAcSite.customerSeq into CustomerAcSiteR
                                                from CustomerAcSiteResult in CustomerAcSiteR.DefaultIfEmpty()
                                                join caa in _dataContext.CustomerAcAddressTable on CustomerAcSiteResult.customerAccountSiteSeq equals caa.customerAccountSiteSeq into caar
                                                from CusAcSiteAddressResult in caar.DefaultIfEmpty()
                                                join CustomerAccountSitePurpose in _dataContext.CustomerAcSitePurposeTable on CustomerAcSiteResult.customerAccountSiteSeq equals CustomerAccountSitePurpose.customerAccountSiteSeq into CustomerAccountSitePurposeR
                                                from CustomerAccountSitePurposeResult in CustomerAccountSitePurposeR.DefaultIfEmpty()
                                                join CustomerAccountSitePurposeType in _dataContext.CustomerAcSitePurposeTypeTable on CustomerAccountSitePurposeResult.sitePurposeTypeSeq equals CustomerAccountSitePurposeType.sitePurposeTypeSeq into CustomerAccountSitePurposeTypeR
                                                from CustomerAccountSitePurposeTypeResult in CustomerAccountSitePurposeTypeR.DefaultIfEmpty()
                                                join GeogrpahyCountry in _dataContext.GeographyTable on CusAcSiteAddressResult.countryGeoSeq equals GeogrpahyCountry.geographySeq into jts1
                                                from GeogrpahyCountry in jts1.DefaultIfEmpty()
                                                join GeogrpahyState in _dataContext.GeographyTable on CusAcSiteAddressResult.stateGeoSeq equals GeogrpahyState.geographySeq into jts2
                                                from GeogrpahyState in jts2.DefaultIfEmpty()
                                                join GeogrpahyCounty in _dataContext.GeographyTable on CusAcSiteAddressResult.countyGeoSeq equals GeogrpahyCounty.geographySeq into jts3
                                                from GeogrpahyCounty in jts3.DefaultIfEmpty()
                                                join GeogrpahyProvince in _dataContext.GeographyTable on CusAcSiteAddressResult.provinceGeoSeq equals GeogrpahyProvince.geographySeq into jts4
                                                from GeogrpahyProvince in jts4.DefaultIfEmpty()
                                                join GeogrpahyCity in _dataContext.GeographyTable on CusAcSiteAddressResult.cityGeoSeq equals GeogrpahyCity.geographySeq into jts5
                                                from GeogrpahyCity in jts5.DefaultIfEmpty()
                                                join GeogrpahyPostalCode in _dataContext.GeographyTable on CusAcSiteAddressResult.postalCodeGeoSeq equals GeogrpahyPostalCode.geographySeq into jts6
                                                from GeogrpahyPostalCode in jts6.DefaultIfEmpty()
                                                join Geogrpahyattribute1 in _dataContext.GeographyTable on CusAcSiteAddressResult.attribute1GeoSeq equals Geogrpahyattribute1.geographySeq into jts7
                                                from Geogrpahyattribute1 in jts7.DefaultIfEmpty()
                                                join Geogrpahyattribute2 in _dataContext.GeographyTable on CusAcSiteAddressResult.attribute2GeoSeq equals Geogrpahyattribute2.geographySeq into jts8
                                                from Geogrpahyattribute2 in jts8.DefaultIfEmpty()

                                                
                                                where a.customerSeq == customerSeq && CusAcSiteAddressResult.customerAccountSiteSeq == CusAcSiteAddressResult.customerAccountSiteSeq


     
                                                select new CustomerDropdownDTO()
                                                {

                                                    label = a.customerId,
                                                    value = a.customerSeq,
                                                    customerName = a.customerName,
                                                    customerAccountSeq = CustomerAcSiteResult.customerAccountSeq,
                                                    customerAccountDescription = CustomerAcResult.customerAccountDescription,
                                                    customerAccountId = CustomerAcResult.customerAccountId,
                                                    customerAccountNo = CustomerAcResult.customerAccountNo,
                                                    customerAccountSiteSeq = CustomerAcSiteResult.customerAccountSiteSeq,
                                                    customerAccountSiteId = CustomerAccountSitePurposeTypeResult.sitePurposeType == "Bill-to" ? CustomerAcSiteResult.customerAccountSiteId : null,
                                                    customerAccountSiteNo = CustomerAcSiteResult.customerAccountSiteNo,
                                                    customerAccountSiteName = CustomerAcSiteResult.customerAccountSiteName,
                                                    customerAccountSitePurposeType = CustomerAccountSitePurposeTypeResult.sitePurposeType,
                                                    countryGeoSeq = CusAcSiteAddressResult.countryGeoSeq,
                                                    customerAccountAddressSeq = CusAcSiteAddressResult.addressSeq,
                                                    customerAccountSiteAddress = CustomerAccountSitePurposeTypeResult.sitePurposeType == "Bill-to" ? CusAcSiteAddressResult.addressLine1
                                                        + (string.IsNullOrEmpty(CusAcSiteAddressResult.addressLine2) ? null : ("," + CusAcSiteAddressResult.addressLine2))
                                                        + (string.IsNullOrEmpty(CusAcSiteAddressResult.addressLine3) ? null : ("," + CusAcSiteAddressResult.addressLine3))
                                                        + (CusAcSiteAddressResult.stateGeoSeq == null ? (string.IsNullOrEmpty(CusAcSiteAddressResult.stateName) ? null : ("," + CusAcSiteAddressResult.stateName)) : (string.IsNullOrEmpty(GeogrpahyState.geographyName) ? null : ("," + GeogrpahyState.geographyName)))
                                                        + (CusAcSiteAddressResult.countryGeoSeq == null ? (string.IsNullOrEmpty(CusAcSiteAddressResult.countryName) ? null : ("," + CusAcSiteAddressResult.countryName)) : (string.IsNullOrEmpty(GeogrpahyCounty.geographyName) ? null : ("," + GeogrpahyCountry.geographyName)))
                                                        + (CusAcSiteAddressResult.provinceGeoSeq == null ? (string.IsNullOrEmpty(CusAcSiteAddressResult.provinceName) ? null : ("," + CusAcSiteAddressResult.provinceName)) : (string.IsNullOrEmpty(GeogrpahyProvince.geographyName) ? null : ("," + GeogrpahyProvince.geographyName)))
                                                        + (CusAcSiteAddressResult.cityGeoSeq == null ? (string.IsNullOrEmpty(CusAcSiteAddressResult.cityName) ? null : ("," + CusAcSiteAddressResult.cityName)) : (string.IsNullOrEmpty(GeogrpahyCity.geographyName) ? null : ("," + GeogrpahyCity.geographyName)))
                                                        + (CusAcSiteAddressResult.countyGeoSeq == null ? (string.IsNullOrEmpty(CusAcSiteAddressResult.countyName) ? null : ("," + CusAcSiteAddressResult.countyName)) : (string.IsNullOrEmpty(GeogrpahyCity.geographyName) ? null : ("," + GeogrpahyCity.geographyName)))
                                                        + (CusAcSiteAddressResult.attribute1GeoSeq == null ? (string.IsNullOrEmpty(CusAcSiteAddressResult.attribute1Name) ? null : ("," + CusAcSiteAddressResult.attribute1Name)) : (string.IsNullOrEmpty(Geogrpahyattribute1.geographyName) ? null : ("," + Geogrpahyattribute1.geographyName)))
                                                        + (CusAcSiteAddressResult.attribute2GeoSeq == null ? (string.IsNullOrEmpty(CusAcSiteAddressResult.attribute2Name) ? null : ("," + CusAcSiteAddressResult.attribute2Name)) : (string.IsNullOrEmpty(Geogrpahyattribute2.geographyName) ? null : ("," + Geogrpahyattribute2.geographyName)))
                                                        + (CusAcSiteAddressResult.postalCodeGeoSeq == null ? (string.IsNullOrEmpty(CusAcSiteAddressResult.postalCode) ? null : ("," + CusAcSiteAddressResult.postalCode)) :
                                                        (string.IsNullOrEmpty(GeogrpahyPostalCode.geographyName) ? null : ("," + GeogrpahyPostalCode.geographyName))) : null,


                                                };

      

        return await query.FirstOrDefaultAsync(c => c.customerAccountSitePurposeType == "Bill-to");

        
    }

I am attempting to retrieve customer details based on customerSeq. If the customer has a site purpose type equal to "Bill-to", the entire customer details are returned. Otherwise, only the CustomerId, CustomerName, and CustomerAccountId are returned. I want to append details for customers when the site purpose type equals one using FirstOrDefault(). Can someone guide me on how to do this?

Answer 1

       IQueryable<CustomerDropdownDTO> query = (from a in _dataContext.CustomerTable

                                                join CustomerAc in _dataContext.CustomerAccountTable on a.customerSeq equals CustomerAc.customerSeq into CustomerAcR
                                                from CustomerAcResult in CustomerAcR.DefaultIfEmpty()

                                                join CustomerAcSite in _dataContext.CustomerAcSiteTable on a.customerSeq equals CustomerAcSite.customerSeq into CustomerAcSiteR
                                                from CustomerAcSiteResult in CustomerAcSiteR.DefaultIfEmpty()

                                                join caa in _dataContext.CustomerAcAddressTable on CustomerAcSiteResult.customerAccountSiteSeq equals caa.customerAccountSiteSeq into caar
                                                from CusAcSiteAddressResult in caar.DefaultIfEmpty()

                                                join CustomerAccountSitePurpose in _dataContext.CustomerAcSitePurposeTable on CustomerAcSiteResult.customerAccountSiteSeq equals CustomerAccountSitePurpose.customerAccountSiteSeq into CustomerAccountSitePurposeR
                                                from CustomerAccountSitePurposeResult in CustomerAccountSitePurposeR.DefaultIfEmpty()

                                                join CustomerAccountSitePurposeType in _dataContext.CustomerAcSitePurposeTypeTable on CustomerAccountSitePurposeResult.sitePurposeTypeSeq equals CustomerAccountSitePurposeType.sitePurposeTypeSeq into CustomerAccountSitePurposeTypeR
                                                from CustomerAccountSitePurposeTypeResult in CustomerAccountSitePurposeTypeR.DefaultIfEmpty()


                                                join GeogrpahyCountry in _dataContext.GeographyTable on CusAcSiteAddressResult.countryGeoSeq equals GeogrpahyCountry.geographySeq into jts1
                                                from GeogrpahyCountry in jts1.DefaultIfEmpty()

                                                join GeogrpahyState in _dataContext.GeographyTable on CusAcSiteAddressResult.stateGeoSeq equals GeogrpahyState.geographySeq into jts2
                                                from GeogrpahyState in jts2.DefaultIfEmpty()


                                                join GeogrpahyCounty in _dataContext.GeographyTable on CusAcSiteAddressResult.countyGeoSeq equals GeogrpahyCounty.geographySeq into jts3
                                                from GeogrpahyCounty in jts3.DefaultIfEmpty()

                                                join GeogrpahyProvince in _dataContext.GeographyTable on CusAcSiteAddressResult.provinceGeoSeq equals GeogrpahyProvince.geographySeq into jts4
                                                from GeogrpahyProvince in jts4.DefaultIfEmpty()

                                                join GeogrpahyCity in _dataContext.GeographyTable on CusAcSiteAddressResult.cityGeoSeq equals GeogrpahyCity.geographySeq into jts5
                                                from GeogrpahyCity in jts5.DefaultIfEmpty()

                                                join GeogrpahyPostalCode in _dataContext.GeographyTable on CusAcSiteAddressResult.postalCodeGeoSeq equals GeogrpahyPostalCode.geographySeq into jts6
                                                from GeogrpahyPostalCode in jts6.DefaultIfEmpty()

                                                join Geogrpahyattribute1 in _dataContext.GeographyTable on CusAcSiteAddressResult.attribute1GeoSeq equals Geogrpahyattribute1.geographySeq into jts7
                                                from Geogrpahyattribute1 in jts7.DefaultIfEmpty()

                                                join Geogrpahyattribute2 in _dataContext.GeographyTable on CusAcSiteAddressResult.attribute2GeoSeq equals Geogrpahyattribute2.geographySeq into jts8
                                                from Geogrpahyattribute2 in jts8.DefaultIfEmpty()

                                                where a.customerSeq == customerSeq && CusAcSiteAddressResult.customerAccountSiteSeq == CusAcSiteAddressResult.customerAccountSiteSeq

                                                select new CustomerDropdownDTO()
                                                {
                                                    label = a.customerId,
                                                    value = a.customerSeq,
                                                    customerName = a.customerName,
                                                    customerAccountSeq = CustomerAcResult.customerAccountSeq,
                                                    customerAccountDescription = CustomerAcResult.customerAccountDescription,
                                                    customerAccountId = CustomerAcResult.customerAccountId,
                                                    customerAccountNo = CustomerAcResult.customerAccountNo,
                                                    customerAccountSiteSeq = CustomerAcSiteResult.customerAccountSiteSeq,
                                                    customerAccountSiteId = CustomerAcSiteResult.customerAccountSiteId,
                                                    customerAccountSiteNo = CustomerAcSiteResult.customerAccountSiteNo,
                                                    customerAccountSiteName = CustomerAcSiteResult.customerAccountSiteName,
                                                    customerAccountSitePurposeType = CustomerAccountSitePurposeTypeResult.sitePurposeType,
                                                    countryGeoSeq = CusAcSiteAddressResult.countryGeoSeq,
                                                    customerAccountAddressSeq = CusAcSiteAddressResult.addressSeq,
                                                    customerAccountSiteAddress = CusAcSiteAddressResult.addressLine1
+ (string.IsNullOrEmpty(CusAcSiteAddressResult.addressLine2) ? null : ("," + CusAcSiteAddressResult.addressLine2))
+ (string.IsNullOrEmpty(CusAcSiteAddressResult.addressLine3) ? null : ("," + CusAcSiteAddressResult.addressLine3))
+ (CusAcSiteAddressResult.stateGeoSeq == null ? (string.IsNullOrEmpty(CusAcSiteAddressResult.stateName) ? null : ("," + CusAcSiteAddressResult.stateName)) : (string.IsNullOrEmpty(GeogrpahyState.geographyName) ? null : ("," + GeogrpahyState.geographyName)))
+ (CusAcSiteAddressResult.countryGeoSeq == null ? (string.IsNullOrEmpty(CusAcSiteAddressResult.countryName) ? null : ("," + CusAcSiteAddressResult.countryName)) : (string.IsNullOrEmpty(GeogrpahyCounty.geographyName) ? null : ("," + GeogrpahyCountry.geographyName)))
+ (CusAcSiteAddressResult.provinceGeoSeq == null ? (string.IsNullOrEmpty(CusAcSiteAddressResult.provinceName) ? null : ("," + CusAcSiteAddressResult.provinceName)) : (string.IsNullOrEmpty(GeogrpahyProvince.geographyName) ? null : ("," + GeogrpahyProvince.geographyName)))
+ (CusAcSiteAddressResult.cityGeoSeq == null ? (string.IsNullOrEmpty(CusAcSiteAddressResult.cityName) ? null : ("," + CusAcSiteAddressResult.cityName)) : (string.IsNullOrEmpty(GeogrpahyCity.geographyName) ? null : ("," + GeogrpahyCity.geographyName)))
+ (CusAcSiteAddressResult.countyGeoSeq == null ? (string.IsNullOrEmpty(CusAcSiteAddressResult.countyName) ? null : ("," + CusAcSiteAddressResult.countyName)) : (string.IsNullOrEmpty(GeogrpahyCity.geographyName) ? null : ("," + GeogrpahyCity.geographyName)))
+ (CusAcSiteAddressResult.attribute1GeoSeq == null ? (string.IsNullOrEmpty(CusAcSiteAddressResult.attribute1Name) ? null : ("," + CusAcSiteAddressResult.attribute1Name)) : (string.IsNullOrEmpty(Geogrpahyattribute1.geographyName) ? null : ("," + Geogrpahyattribute1.geographyName)))
+ (CusAcSiteAddressResult.attribute2GeoSeq == null ? (string.IsNullOrEmpty(CusAcSiteAddressResult.attribute2Name) ? null : ("," + CusAcSiteAddressResult.attribute2Name)) : (string.IsNullOrEmpty(Geogrpahyattribute2.geographyName) ? null : ("," + Geogrpahyattribute2.geographyName)))
+ (CusAcSiteAddressResult.postalCodeGeoSeq == null ? (string.IsNullOrEmpty(CusAcSiteAddressResult.postalCode) ? null : ("," + CusAcSiteAddressResult.postalCode)) : (string.IsNullOrEmpty(GeogrpahyPostalCode.geographyName) ? null : ("," + GeogrpahyPostalCode.geographyName))),

                                                });


       if (query != null)
       {
           if (query.Any(s => s.customerAccountSitePurposeType == "Bill-to"))
           {
               query = query.Select(dto => new CustomerDropdownDTO
               {

                   label = dto.label,
                   value = dto.value,
                   customerName = dto.customerName,
                   customerAccountSeq = dto.customerAccountSeq,
                   customerAccountDescription = dto.customerAccountDescription,
                   customerAccountId = dto.customerAccountId,
                   customerAccountNo = dto.customerAccountNo,
                   customerAccountSiteSeq = dto.customerAccountSiteSeq,
                   customerAccountSiteId = dto.customerAccountSiteId,
                   customerAccountSiteNo = dto.customerAccountSiteNo,
                   customerAccountSiteName = dto.customerAccountSiteName,
                   customerAccountSitePurposeType = dto.customerAccountSitePurposeType,
                   countryGeoSeq = dto.countryGeoSeq,
                   customerAccountAddressSeq = dto.customerAccountAddressSeq,
                   customerAccountSiteAddress = dto.customerAccountSiteAddress
               }).Distinct();

           }
           else
           {
               query = query.Select(dto => new CustomerDropdownDTO
               {

                   label = dto.label,
                   value = dto.value,
                   customerName = dto.customerName,
                   customerAccountId = dto.customerAccountId,
                   customerAccountSeq = dto.customerAccountSeq,
               });

           }
       }

       var result = await query.FirstOrDefaultAsync();

       return result;
       ```

***From Acquired result we can apply condition based on our scenario***