CODEWITHSUNDEEP
c#sqllinqlinq-to-sqllinq-to-objects
Sev
Sev

Reputation: 781

Method call in LINQ column

I have the following LINQ query:

//myRepository.GetClients(); returns an IQueriable<>
        var AllClients = myRepository.GetClients();


        var ClientReports = from c in FirmECFIs
                         select new ClientReport
                        {
                            FirmSerialNumber = c.SerialNumber,
                            FirmName = c.Name,
                            Year = c.Year,
                            Month = c.Month,
                            CustID = c.CustID,
                            TotalCount =myRepository.GetCount(c.SerialNumber, billingYear, billingMonth),
                            TotalPrice = "0"
                        };

I get an error that says "...has no supported translation to SQL" Where I remove the method call from TotalCount column and assign a static value it works fine. Looks liek LINQ is not able to translate the method call to tSQL

Can anyone help me out with this?

Thanks

Source of GetCount() method:

        public int GetCount(int SerialNumber, string billingYear, string billingMonth)
    {
        var Count = CreateDataContext().myView.Where(c => c.SerialNumber == SerialNumber)
            .Where(c => c.Year == Convert.ToInt32(billingYear)).Where(c => c.Month == Convert.ToInt32(billingMonth));
        return Count.ToList().Count;

Upvotes: 3

Views: 212

Answers (2)

BrokenGlass
BrokenGlass

Reputation: 160902

Your repository doesn't really do its job here very well - in fact it seems to be a very leaky abstraction that at best doesn't help you much (since you are still querying outside the repository).

One approach would be introducing a method GetClientReports to your repository that produces this result for you. Assuming this is just Linq to Sql underneath you can just use a nested query or join at that level.

Upvotes: 3

AD.Net
AD.Net

Reputation: 13399

You can execute another SQL/LINQ query in that line, but you cannot call another C# function. It cannot translate that into SQL.

Upvotes: 2

Related Questions