LINQ DataLoadOptions - Retrieval of data via Fulltext/Broken Foreign Key Relationship
- by Alex
I've hit a brick wall:
I have an SQL Function:
FUNCTION [dbo].[ContactsFTS]
(@searchtext nvarchar(4000))
RETURNS TABLE
AS
RETURN
SELECT * FROM Contacts
INNER JOIN CONTAINSTABLE(Contacts, *, @searchtext)
AS KEY_TBL ON Contacts.Id = KEY_TBL.[KEY]
which I am calling via LINQ
public IQueryable<ContactsFTSResult> SearchByFullText(String searchText)
{
return db.ContactsFTS(searchText);
}
I am projecting the ContactsFTSResult collection into a List<Contact> which is then given to my viewmodel.
Here is the problem: My Contacts table (and therefore the Contact object created via LINQ to SQL) has multiple FK relationships to other information, such as Contact.BillingAddressId is an FK to an Address.Id. That information is missing after I do the fulltext search (e.g. if I try to access Contact.BillingAddress it is null).
Can I add this information somehow via DataLoadOptions? I tried LoadWith<Contact>(c => c.BillingAddress) but this doesn't work, I assume because of the fact that I'm calling the function instead of doing the whole query via LINQ.
