LINQ to SQL cross apply

Question

I would like to create a query with a cross apply into a user defined table value function in LINQ. The SQL would be really rather simple as below:

SELECT *
FROM MyTable mt
CROSS APPLY MyTVF(mt.id)

This post gives an example of a LINQ query that results in generated sql that contains both a cross apply and an outer apply but only for a sub query not for a tvf. This article confirms that LINQ to SQL will generate cross apply and outer apply operators for "relationship navigations" but I am not sure what that means in this context. This post describes pretty much what I want to do and the answer says the only way to do this is to wrap the SQL query in a stored procedure and then call the sp via LINQ. I hope that this is not true because I actually need a tvf that can be used in this way throughout an application in multiple LINQ queries so "wrap it in an sp" would not work for me. Does anyone know of a way to get something like the simple SQL statement above via LINQ?

Gerstein

keep in mind, that relation between linq and SQL isn't surjective, so not every SQL command can be interpreted via linq. – Isis

Answer 1

How about this:

from mt in db.MyTable
from mf in db.MyTVF (mt.id)
select new { mt.Blah, mf.Blah }

Suds

Yeah that totally works, i can't beleive i didn't even try that. Nice one thanks. – Gerstein

Answer 2

    var query = ActivityRepository.Where(p => p.iAction > -1 && userIds.Contains(p.iSellerId)).GroupBy(c => c.iSellerId).Select(c => c.OrderByDescending(cc => cc.dBeginTime).First()).Select(a => new ActivityInfo
        {
            ActivityId = a.iActivityId,
            StartTime = a.dBeginTime,
            SellerId = a.iSellerId,
            EndTime = a.dEndTime,
            ActivityName = a.sName,
        });

the keypoint is :.Select(c => c.OrderByDescending(cc => cc.dBeginTime).First())

Chen

Interesting to know. I've pretty much moved over to EF these days, haven't used LinqToSQL for a couple of years. – Gerstein