CODEWITHSUNDEEP
c#.netlinqlinq-to-sql
Shehab Fawzy
Shehab Fawzy

Reputation: 7808

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

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();
        }
    }

Upvotes: 8

Views: 550

Answers (5)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79919

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
              };

Upvotes: 0

Totem
Totem

Reputation: 474

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.

Upvotes: 0

Anders Abel
Anders Abel

Reputation: 69260

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.

Upvotes: 6

Tigran
Tigran

Reputation: 62248

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.

Upvotes: 0

George Duckett
George Duckett

Reputation: 32428

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?

Upvotes: 3

Related Questions