LINQ Query Help

Hi, not sure if I can explain this properly, but here I go:

I have a Table "Fundraiser_Participants" where I have people that participate in fundraisers. I also have another table "Fundraiser_Orders" where I have a Foreign Key back to Fundraiser Participants where I store all the participant's orders.

I was wanting to do a "search" on my page for any "item" ordered to display the person who ordered them. But I can't figure out how to do the where clause.

Thoughts?

I was thinking something like this but just can't figure it out:

var query = _fundraiserParticipantsReposity.Table;

            query = from p in query
                    join psa in _fundraiserFundraiserOrderReposity.Table on p.Id equals psa.FK_FundraiserParticipant
                    where p.FK_FundraiserID == fid
                    && psa.GraphicLetter.Contains(SearchGraphicLetter)
                    orderby p.Id descending
                    select p;

            if (SearchGraphicLetter != "")
            {
                query = query.Where(c => _fundraiserFundraiserOrderReposity.Table.Where(psa => psa.GraphicLetter.Contains(SearchGraphicLetter)).Join(c.Id, psa.);
            }

Not sure if this is the best, but here is what I ended up doing:

SearchGraphicLetter = SearchGraphicLetter == null ? "" : SearchGraphicLetter;
            SearchItemColor = SearchItemColor == null ? "" : SearchItemColor;
            SearchItemNumber = SearchItemNumber == null ? "" : SearchItemNumber;
            SearchItemSize = SearchItemSize == null ? "" : SearchItemSize;
            SearchParticipant = SearchParticipant == null ? "" : SearchParticipant;

            var query = _fundraiserParticipantsReposity.Table;

            query = from p in query
                    join psa in _fundraiserFundraiserOrderReposity.Table on p.Id equals psa.FK_FundraiserParticipant into joined
                    from j in joined.DefaultIfEmpty()
                    where p.FK_FundraiserID == fid && j == null ? true :
                    p.FK_FundraiserID == fid
                    && p.ParticipantName.Contains(SearchParticipant)
                    && j.GraphicLetter.Contains(SearchGraphicLetter)
                    && j.ItemColor.Contains(SearchItemColor)
                    && j.ItemNumber.Contains(SearchItemNumber)
                    && j.ItemSize.Contains(SearchItemSize)
                    orderby p.Id descending
                    select p;

For what it's worth, this is how I do similar, and you should be able to do same if you've mapped the child entities in EF code.  I don't know why you are doing outer join (and then also having that "... == null ? true..."), since I assume that a fundraiser order must have a valid/linked fundRaiser participant.

var orderItems = _orderItemRepository.Table;

if (!String.IsNullOrEmpty(searchParticipant))
    orderItems = orderItems.Where(oi => oi.Order.Customer.Username.Contains(searchParticipant));

if (!String.IsNullOrEmpty(searchProductName))
    orderItems = orderItems.Where(oi => oi.Product.Name.Contains(searchProductName));

...

var customers = orderItems
    .Select(oi => oi.Order.Customer)
    .Distinct()
    .OrderByDescending;

And, not to be a nit pick ;) but ...

Reposity  is spelled wrong  (--> Repository  )

Local vars should be camel case  (e.g. SearchItemColor  -> searchItemColor )

There is a short cut for
  SearchGraphicLetter = SearchGraphicLetter == null ? "" : SearchGraphicLetter
is
  SearchGraphicLetter = SearchGraphicLetter ?? "";
But, as per above, my preference (and you'd also see similar in the core code) is to only include the WHERE clauses that are needed.

You should use Distinct

Thanks mate

How do I map the children?