trying to call a method in the where of a linq statment

Question

Below is the code I'm using but it replies with

Method 'Boolean isUser(System.String)' has no supported translation to SQL.

Any help? Btw I'm using linq to SQL data source

public void dataBind()
{
    using (var gp = new GreatPlainsDataContext())
    {
        var emp = from x in gp.Employees
                  let k = isUser(x.ID)
                  where x.ActivtyStatus == 0
                  && isUser(x.ID) != false
                  orderby x.ID
                  select new
                  {
                      ID = x.ID,
                      Name = x.FirstName + " " + x.MiddleName
                  };
        ListView1.DataSource = emp;
        ListView1.DataBind();
    }
}

public static bool isUser(string ID)
{
    int temp;
    bool x = int.TryParse(ID, out temp);
    return x;
}

I found a solution to query the result of the first query as objects but is that good cause I will passing through my data twice.

the updated code that worked in the end after using the like as advised by Anders Abel

public void dataBind()
    {
        using (var gp = new GreatPlainsDataContext())
        {
            var emp = from x in gp.Employees
                      where x.ActivtyStatus == 0
                      && SqlMethods.Like(x.ID, "[0-9]%")
                      orderby x.ID
                      select new
                      {
                          ID = x.ID,
                          Name = x.FirstName + " " + x.MiddleName
                      };
            ListView1.DataSource = emp;
            ListView1.DataBind();
        }
    }

What are the possible values for Employees.ID, and why does a string that is parseable to an int mean that an employee is a user? — spender, Jan 11 '12 at 09:14
@spender regarding the what part a user will have an id like "11064" will a team will have "oss". regarding the why part I came and the system was built like that on great plains :( — Shehab Fawzy, Jan 11 '12 at 09:20
The legacy system curse in action once a gain. If checking if it's only numbers in the field is enough, you can use a LIKE expression. I've updated my answer with info and a link. — Anders Abel, Jan 11 '12 at 09:26

score 6 · Accepted Answer · edited May 23 '17 at 12:21

6

Linq-to-sql translates the query into SQL. It only knows how to translate a limited set of built in functions. You have to rewrite your query to not include a function of your own.

A complete list of the linq-to-sql supported functions and operators can be found at MSDN.

You can use SqlMethods.Like() to check if the field only contains digits. See T-SQL IsNumeric() and Linq-to-SQL for an example.

edited May 23 '17 at 12:21

Community

1
1

answered Jan 11 '12 at 09:13

Anders Abel

67,989
17
150
217

+1 the UDF at the given link using `IsNumeric` is probably the best solution in this case. If it used often a View or Computed Column may be of use here. – Hux Jan 11 '12 at 09:27

score 3 · Answer 2 · answered Jan 11 '12 at 09:13

The problem you're having is that because the query needs to be ran on the database, you can only use things that will work on the database, the C# code in your isUser method can't run on the database.

You'll have to re-write it without using that function. Perhaps you have a table listing users by IDs you could join on?

score 0 · Answer 3 · answered Jan 11 '12 at 09:22

Would be easier to you query for ID valid for you, for example where ID > 0, recover results, and after on the resulted collection execute the hypothetical filter you want with

isUser(x.ID)

cause, I presume, it executes more complex validation somehow.

Important, that from DB results are less as possible, to avoid data transmission latency.

score 0 · Answer 4 · answered Jan 11 '12 at 09:31

You could call this code:

var emp = from x in gp.Employees
                  where x.ActivtyStatus == 0
                  orderby x.ID

And obtain a valid result, since every linq expression can be applied to Employees through linq to sql. Then you can cast emp to a list or and array and filter the collection using your method isUser.

score 0 · Answer 5 · answered Jan 11 '12 at 10:41

As an alternative solution you can call any sql UDF(user defined function) in-line within LINQ queries like gp.isUser(x.ID), so you can define this function isUser as a SQL UDF, something like:

 CREATE FUNCTION isUser(@id int)
 RETURNS bit;
 AS
 BEGIN
    if exists(select * from Users where userId = @id)
    begin 
          return 1; --true the user exists;
    else 
          return 0; --false doesn't exist;
    end
 end

Then you have to define this UDF in your .DBML file that contains other tables and procedures definition mappings. Then you can Call it or any other UDF function inline within your LINQ query Like this:

var emp = from x in gp.Employees
              let k = gp.isUser(x.ID)
              where x.ActivtyStatus == 0
              && gp.isUser(x.ID) != 1
              orderby x.ID
              select new
              {
                  ID = x.ID,
                  Name = x.FirstName + " " + x.MiddleName + " " + x.LastName
              };

trying to call a method in the where of a linq statment

5 Answers5