Bdtjtk

I'm not sure if this is the right forum, if not please move it
My office is in the process of re-writing our app and switching from VB to C# and the entity framework. I've had some success in joining multiple tables all with left outer joins but I'm at my limits on how to convert an sql view like the following:

SELECT        dbo.Addresses.AddressId, dbo.AddressTypes.AddressType, dbo.Addresses.AddressTypeId, dbo.Addresses.ParentAddressId, dbo.Addresses.AddressCode, dbo.Addresses.AddressNumber, dbo.Addresses.Address, 

                         dbo.Addresses.SubAddress, dbo.Addresses.Direction, dbo.Addresses.City, dbo.Addresses.StateId, dbo.Addresses.CountryId, ISNULL(dbo.Addresses.AddressNumber + ' ', '') + ISNULL(dbo.Addresses.Direction + ' ', '') 

                         + ISNULL(dbo.Addresses.Address + ' ', '') + ISNULL(dbo.Addresses.Suffix + ' ', '') + ISNULL(dbo.Addresses.SubAddress + ' ', '') AS FullAddress, dbo.Addresses.RegionId, dbo.Addresses.CountyId, 

                         dbo.Addresses.OccupancyTypeId, dbo.Addresses.PropertyUseTypeId, dbo.Addresses.Comment, dbo.States.StateAbbr, dbo.States.State, dbo.Regions.Region, dbo.Regions.RegionCode, dbo.Counties.County, 

                         dbo.Counties.CountyCode, dbo.Countries.Country, dbo.OccupancyTypes.OccupancyType, dbo.OccupancyTypes.OccupancyTypeCode, dbo.PropertyUseTypes.PropertyUseType, dbo.Party.PartyName, 

                         dbo.PropertyUseTypes.PropertyUseTypeCode, dbo.UserDefFields.UserDefFieldId, dbo.UserDefFields.FieldDesc, dbo.UserDefValues.UserDefValueId, dbo.UserDefValues.UserDefValue, dbo.Addresses.ZipId, dbo.Zips.Zip, 

                         dbo.AddressParties.PartyID, dbo.Addresses.Latitude, dbo.Addresses.Longitude, dbo.Addresses.Inactive, dbo.Addresses.DefaultPass, dbo.Addresses.Suffix, dbo.AddressTypes.AgencyId, dbo.Addresses.LegalDesc, 

                         dbo.AddressParties.Inactive AS PAInactive, dbo.AddressParties.RoleTypeId, dbo.Addresses.POBox, dbo.AddressParties.ExternalValue, dbo.Addresses.DateUpdated, dbo.Addresses.DateInserted, dbo.Addresses.ReportId, 

                         dbo.Addresses.Map, dbo.Addresses.Block, dbo.Addresses.Lot, dbo.Addresses.TaxParcel, dbo.Addresses.ExternalId, dbo.Addresses.Schedule

FROM            dbo.UserDefFields RIGHT OUTER JOIN

                         dbo.UserDefValues ON dbo.UserDefFields.UserDefFieldId = dbo.UserDefValues.UserDefFieldId RIGHT OUTER JOIN

                         dbo.Party RIGHT OUTER JOIN

                         dbo.Addresses LEFT OUTER JOIN

                         dbo.Zips ON dbo.Addresses.ZipId = dbo.Zips.ZipId LEFT OUTER JOIN

                         dbo.AddressTypes ON dbo.Addresses.AddressTypeId = dbo.AddressTypes.AddressTypeId LEFT OUTER JOIN

                         dbo.States ON dbo.Addresses.StateId = dbo.States.StateId LEFT OUTER JOIN

                         dbo.Regions ON dbo.Addresses.RegionId = dbo.Regions.RegionId LEFT OUTER JOIN

                         dbo.Counties ON dbo.Addresses.CountyId = dbo.Counties.CountyId LEFT OUTER JOIN

                         dbo.Countries ON dbo.Addresses.CountryId = dbo.Countries.CountryId LEFT OUTER JOIN

                         dbo.OccupancyTypes ON dbo.Addresses.OccupancyTypeId = dbo.OccupancyTypes.OccupancyTypeId LEFT OUTER JOIN

                         dbo.PropertyUseTypes ON dbo.Addresses.PropertyUseTypeId = dbo.PropertyUseTypes.PropertyUseTypeId LEFT OUTER JOIN

                         dbo.AddressParties ON dbo.Addresses.AddressId = dbo.AddressParties.AddressID ON dbo.Party.PartyID = dbo.AddressParties.PartyID ON dbo.UserDefValues.RecordId = dbo.Addresses.AddressId

Into something like the following:

   public List<AddressPartyList> GetAddressPartyList(Guid? AddressId, Guid? RoleTypeId = null, bool ShowInactive = false, bool FromWebOnly = false, bool WebAcceptedOnly = false, bool WebRejectedOnly = false)

    {

        IQueryable<AddressPartyList> thisList;



        thisList = myappContext.myappData().AddressParties.Join(

                   myappContext.myappData().RoleTypes, ap => ap.RoleTypeId, rt => rt.RoleTypeId, (ap, rt) => new { ap, rt }).Join(

                   myappContext.myappData().Parties, apr => apr.ap.PartyId, p => p.PartyId, (apr, p) => new AddressPartyList

                   {

                       AddressId =  apr.ap.AddressId,

                       PartyId = apr.ap.PartyId,

                       AccountId =  p.AccountId,

                       RoleType =  apr.rt.RoleType1,

                       Salutation = p.Salutation,

                       FirstName = p.FirstName,

                       MiddleInitial = p.MiddleInitial,

                       LastName = p.LastName,

                       Suffix = p.Suffix,

                       PartyName = p.PartyName,

                       Email = p.Email,

                       Comment = p.Comment,

                       ExternalId = p.ExternalId,

                       PartyInactive = p.Inactive,

                       WebAccountId = p.WebAccountId,

                       DateUpdated = p.DateUpdated,

                       DateInserted = p.DateInserted,

                       PriceLevel = p.PriceLevel,

                       FromWeb = p.FromWeb,

                       WebAccepted = p.WebAccepted,

                       WebRejected = p.WebRejected,

                       RoleTypeId = apr.ap.RoleTypeId,

                       InactiveAtAddress = apr.ap.Inactive,

                       IsBus = p.IsBus,

                       Sequence = apr.ap.Sequence,

                       AddressPartyId = apr.ap.AddressPartyId



                   }).Where(p => p.AddressId == AddressId);





        if (!ShowInactive)

        {

            thisList = thisList.Where(p => p.PartyInactive == false && p.InactiveAtAddress == false);

        }



        if (RoleTypeId != null)

        {

            thisList = thisList.Where(p => p.RoleTypeId == RoleTypeId);

        }



        if (FromWebOnly)

        {

            thisList = thisList.Where(p => p.FromWeb == true);

        }

        if (WebAcceptedOnly)

        {

            thisList = thisList.Where(p => p.WebAccepted == true);

        }

        if (WebRejectedOnly)

        {

            thisList = thisList.Where(p => p.WebRejected == true);

        }



        return thisList.GroupBy(p => p.PartyId).Select(p => p.FirstOrDefault()).OrderBy(p => p.Sequence).ThenBy(p => p.PartyName).ToList();

    }

Any help would be appreciated. Also, what would be the syntax for a view with multiple right and left outer joins. I didn't code it, I'm just trying to convert it and I understand it's best to use left outer joins.
Thanks.