none
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.

    Monday, December 16, 2013 5:47 AM

Answers

  • 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.

    Monday, December 16, 2013 9:15 AM
    Moderator

All replies

  • 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.

    Monday, December 16, 2013 9:15 AM
    Moderator
  • 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

             );

         }

    }                                                              

     

     

                                                   

               

                       


    • Edited by WandrewU Monday, December 16, 2013 9:10 PM
    Monday, December 16, 2013 9:06 PM
  • 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.

    Tuesday, December 17, 2013 8:48 AM
    Moderator
  • Hi, I cannot find SqlMethods.like from intellisens. where can  I get that?
    Tuesday, December 17, 2013 9:43 AM
  • 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.

    Tuesday, December 17, 2013 10:03 AM
    Moderator
  • in your code db.isnull, what will I use to replace db?

    Many thanks,

    Tuesday, December 17, 2013 10:29 AM
  • 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.

    Wednesday, December 18, 2013 6:52 AM
    Moderator
  • Hi, the intellisense doesn't show isnull function after unitofwork.

    Wednesday, December 18, 2013 9:34 PM
  • 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.

    Thursday, December 19, 2013 8:59 AM
    Moderator
  • Hi,

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

    Friday, December 20, 2013 2:46 AM
  • 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.

    Friday, December 20, 2013 11:43 AM
    Moderator