1

I am using linq to sql. I am running a query which uses a function to return the right record. I am using a function so I can use if statement which I dont know how to do it within the linq clause. The code is copied below. It returns an error: "GetPageOwner(int 32) has no supported translation to sql" what am i doing wrong? How to fix it to get the same results?

   return (from page select new Result
        {

            pageOwner = GetPageOwner(page.page_id)
        });


    public Post GetPageOwner(int pageid)
    {

        var posts = (from dp in db.Posts where dp.pageid == pageid select dp);
        var returned = posts;

        if (posts.Count() > 0)
        {
            var latest = posts.OrderByDescending(o => o.Date).FirstOrDefault();
            var sharedsamedayaslatest = (from p in posts where p.Date.AddDays(1) >= latest.Date select p);
            if (sharedsamedayaslatest.Count() > 1)
            {
                var followedpost = (from p in posts from s in db.Subscriptions where s.Subscriber == UID && s.Subscribedto == p.UserId select p);
                var count = followedpost.Count();
                if (count == 1)
                {
                    returned = followedpost;
                }
                else if (count > 1)
                {
                    returned = (from s in followedpost let reposts = GetPostReposts(s.id) let rating = GetPostRating(s.id) let score = reposts + rating orderby score descending select s);
                }
                else
                { 
                    //no follower shared this post so return the most liked
                    returned = (from s in sharedsamedayaslatest let reposts = GetPostReposts(s.id) let rating = GetPostRating(s.id) let score = reposts + rating orderby score descending select s);
                }
            }
            else
            {
                //no shares on the day the latest share
                returned = sharedsamedayaslatest;
            }
        }
        else
        {
            //only one post
            returned = posts;
        }


        return returned.FirstOrDefault(); //order by userid gets a random one



    }
Marwan Roushdy
  • 1,214
  • 12
  • 25

1 Answers1

3

Linq to sql has to translate functions from .net into sql. Not all .net functions have an equivalent in sql, and ones written by you definitely don't have one.

That being said you can implement your function as a stored procedure and use it in your linq query.

linkerro
  • 5,318
  • 3
  • 25
  • 29
  • how can I fix this? Is there a way to do a stored procedure using c#? I have very little knowledge on actual sql.. – Marwan Roushdy Feb 20 '12 at 08:10
  • There is a way to integrate c# with sql but I think it's a bad idea. If you don't know sql delegate the task to someone who does. If you can't do that you can extract the function out of the query and run it in a foreach loop, which would work. The only problem would be you would have x+1 hits to the db instead of just 1. – linkerro Feb 20 '12 at 08:47
  • I cant run it in a foreach because its on a lot of rows and it would consume a lot of memory. Do you know how to write if statements in linq? this would solve the problem as I would not need the function. – Marwan Roushdy Feb 20 '12 at 08:49