LINQ DataLoadOptions - Retrieval of data via Fulltext/Broken Foreign Key Relationship

Posted by Alex on Stack Overflow See other posts from Stack Overflow or by Alex
Published on 2010-06-01T21:29:22Z Indexed on 2010/06/01 21:33 UTC
Read the original article Hit count: 312

Filed under:
|
|
|
|

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.

© Stack Overflow or respective owner

Related posts about c#

Related posts about sql