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

Kesava Subhash Gullapudi 30 Reputation points
2023-11-25T11:00:49.01+00:00
    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?

Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
724 questions
.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,609 questions
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,373 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,616 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Kesava Subhash Gullapudi 30 Reputation points
    2023-11-28T06:57:29.0266667+00:00
           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***  
    
    
    1 person found this answer helpful.
    0 comments No comments