LinQ to Entities, How to return input type null values on a search result

Question

Hi All,

How do I interpret this code to LinQ Extension Method.

declare

@meterId_nvarchar(10)=''

select*from[Flowmeter_Dev].[dbo].[MeterSites]

whereClientID=3 AND(REPLACE(ISNULL(MeterId,''),0,'')like'%'+ISNULL(@meterId_,'%')+'%')

Thanks in Advance.

Answer 1

Hello,

>>How do I interpret this code to LinQ Extension Method.

Do you mean that you want translate below code to be corresponding linq query?

If it is, we can have a try to use codes like below:

Public IQuerable<Order>  GetData(string orderCode)

{

                var result = from order in db.Orders

                             where order.OrderID == 3 && SqlMethods.Like(db.IsNull(order.OrderCode, "").Replace("0", ""), "%" + orderCode == null ? "%" : orderCode + "%")

select order;

return result;

}

The IsNull method is defined in the DataClasses1DataContext:

public partial class DataClasses1DataContext

{

    //…

    [Function(Name = "IsNull", IsComposable = true)]

    [return: Parameter(DbType = "NVarChar(MAX)")]

    public string IsNull(

        [Parameter(Name = "field", DbType = "NVarChar(MAX)")] string field,

        [Parameter(Name = "output", DbType = "NVarChar(MAX)")] string output)

    {

        return ((string)(this.ExecuteMethodCall(this,

                ((MethodInfo)(MethodInfo.GetCurrentMethod())),

                field, output).ReturnValue));

    }

}

If I have misunderstood, please let me know.

Regards.

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 2

Hi, thanks for your reply. I was hoping that the code will be inserted as indicated below. How can I do these? Thanks in advance.

publicActionResultGridDemoData(stringsidx, stringsord, intpage, introws, stringmeterid,  stringselectedmeterlocation, stringfromdate, stringtodate, stringclient, stringbasin)

{

var meterReadings = unitOfWork.MeterReadingRepository.Get();

if(client != "")

      {

  meterReadings = unitOfWork.MeterReadingRepository.Get(r =>

                                          r.MeterSite.Client.ClientName == client &&

                                       //   code goes hereinstead of this below which                                 does not accept null values

                                     //           r.MeterSite.MeterId == selectedmeterlocation

         );

     }

}                                                              

　

　

                                               

           

                   

Answer 3

Have a try to use code like below:

string orderCode = null;
                var result = db.Orders.Where(order=> order.OrderID == 3 && SqlMethods.Like(db.IsNull(order.OrderCode, "").Replace("0", ""), "%" + orderCode == null ? "%" : orderCode + "%")).ToList();

It actually is similar with the first way I posted.

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 4

Hi, I cannot find SqlMethods.like from intellisens. where can  I get that?

Answer 5

Hi,

It is under System.Data.Linq.SqlClient namespace. Adding codes like below on the top of the .cs file.

using System.Data.Linq.SqlClient;

Regards.

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 6

in your code db.isnull, what will I use to replace db?

Many thanks,

Answer 7

Hi,

For this, please see my first post, it is a custom method written the datacontext class file.

Since you have used the repository to package the datacontext, you need to expose a method in repository class to call the IsNull method and then use the example of repository class to call the method.

I assume the example of repository class is unitOfWork and then we can call it like below:

unitOfWork.IsNull(param1,param2) 

Regards.

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 8

Hi, the intellisense doesn't show isnull function after unitofwork.

Answer 9

Have you write a method in unitOfWork class which to call the IsNull method which is defined in yourcontext like

public partial class yourcontext
{
//...
 [Function(Name = "IsNull", IsComposable = true)]

    [return: Parameter(DbType = "NVarChar(MAX)")]

    public string IsNull(

        [Parameter(Name = "field", DbType = "NVarChar(MAX)")] string field,

        [Parameter(Name = "output", DbType = "NVarChar(MAX)")] string output)

    {

        return ((string)(this.ExecuteMethodCall(this,

                ((MethodInfo)(MethodInfo.GetCurrentMethod())),

                field, output).ReturnValue));

    }
}

public class UnitWotrk
{
//...
public string IsNumll(param1,param2)
{
_yourcontetx.IsNull(param1,param2);
}
}

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 10

Hi,

What assembly should I add in the using statements? it shows the red squigly lines. Many thanks.

Answer 11

using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Reflection;

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.