locked
How to use Clause "IN" RRS feed

  • Question

  • User-893002196 posted

    Hi All,

    How can I passed array companies id into the .Where highlight orange query below:-

    var companies = companies.Where(c=>!c.Id.Equals(0)).Select(c => c.Id).Distinct().ToList(); 
                RequestEntityDataSource.Where = $"it.Company.Id IN (String.Join(",",{companies} )) && " + 
                                                      $"(it.Status = {(int)Status.Saved} || it.status = {(int)Status.Ordered}) && " +
                                                      "(it.Id = @IdFilter || @IdFilter = -1) &&" +
                                                      "(it.CreatedBy LIKE '%' + @createdByFilter + '%' || @createdByFilter is null) &&" +
                                                      "(it.PtName LIKE '%' + @pNameFilter + '%' || @PNameFilter is null) &&" +
                                                      "(it.EndUser LIKE '%' + @endUserFilter + '%' || @endUserFilter is null) &&" +
                                                      "(it.Name LIKE '%' + @NameFilter + '%' || @NameFilter is null)";

    Error:

    The query syntax is not valid. Near term ',', line 6, column 23.

    Please advise.

    Thanks

    Regards,

    Micheale

    Friday, May 28, 2021 1:46 PM

All replies

  • User-821857111 posted
    IN ({String.Join(",",{companies} )})
    Friday, May 28, 2021 5:33 PM
  • User-893002196 posted

    Hi Sir,

    Thanks. Still not working.
    I modified the code as:-


    var companies = String.Join(",",companies.Where(c=>!c.Id.Equals(0)).Select(c => c.Id).Distinct().ToList()); 
                RequestEntityDataSource.Where = $"it.Company.Id IN ({companies}) && " + 
                                                      $"(it.Status = {(int)Status.Saved} || it.status = {(int)Status.Ordered}) && " +
                                                      "(it.Id = @IdFilter || @IdFilter = -1) &&" +
                                                      "(it.CreatedBy LIKE '%' + @createdByFilter + '%' || @createdByFilter is null) &&" +
                                                      "(it.PtName LIKE '%' + @pNameFilter + '%' || @PNameFilter is null) &&" +
                                                      "(it.EndUser LIKE '%' + @endUserFilter + '%' || @endUserFilter is null) &&" +
                                                      "(it.Name LIKE '%' + @NameFilter + '%' || @NameFilter is null)";

    I still getting same error: The query syntax is not valid. Near term ',', line 6, column 23.

    I believe ithe parameter should be in this way it.Company.Id IN (120,150) , but passed in IN({string}). How can I transform the {companies} to be an select sql split the arrays to be single columns data to handle this kind parameter?

    .Where = $"it.Company.Id IN (SELECT * FROM [dbo].[fnSplit]({companies},","))  && " +

    CREATE FUNCTION [dbo].[fnSplit](
        @sInputList VARCHAR(8000) -- List of delimited items
      , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
    ) RETURNS @List TABLE (item VARCHAR(8000))
    
    BEGIN
    DECLARE @sItem VARCHAR(8000)
    WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
     BEGIN
     SELECT
      @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
      @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
    
     IF LEN(@sItem) > 0
      INSERT INTO @List SELECT @sItem
     END
    
    IF LEN(@sInputList) > 0
     INSERT INTO @List SELECT @sInputList -- Put the last item in
    RETURN
    END

    I tried this also no luck, cannot passed to IN(<<calling sql function>>)

    I tried this too but no luck:-

    var companies = companies.Where(c=>!c.Id.Equals(0)).Select(c => c.Id).Distinct().ToList(); 
    int[] markers = new int[companies.Count()];
                for (int i = 0; i < markers.Length; i++)
                    markers[i] = companies[i];

    RequestEntityDataSource.Where = $"it.Company.Id IN ({markers}) && " + $"(it.Status = {(int)Status.Saved} || it.status = {(int)Status.Ordered}) && " + "(it.Id = @IdFilter || @IdFilter = -1) &&" + "(it.CreatedBy LIKE '%' + @createdByFilter + '%' || @createdByFilter is null) &&" + "(it.PtName LIKE '%' + @pNameFilter + '%' || @PNameFilter is null) &&" + "(it.EndUser LIKE '%' + @endUserFilter + '%' || @endUserFilter is null) &&" + "(it.Name LIKE '%' + @NameFilter + '%' || @NameFilter is null)";

    I tried this also no luck:

    var companies = String.Join(",",companies.Where(c=>!c.Id.Equals(0)).Select(c => c.Id).Distinct().ToList());
    
    RequestEntityDataSource.Where = $"it.Company.Id IN ({companies}.Split(',').Select(s => s)) && " + $"(it.Status = {(int)Status.Saved} || it.status = {(int)Status.Ordered}) && " + "(it.Id = @IdFilter || @IdFilter = -1) &&" + "(it.CreatedBy LIKE '%' + @createdByFilter + '%' || @createdByFilter is null) &&" + "(it.PtName LIKE '%' + @pNameFilter + '%' || @PNameFilter is null) &&" + "(it.EndUser LIKE '%' + @endUserFilter + '%' || @endUserFilter is null) &&" + "(it.Name LIKE '%' + @NameFilter + '%' || @NameFilter is null)";

    Refer to this:

    https://forums.asp.net/t/2018683.aspx?Entitydatasource+filter+with+IN+clause

    I tried hardcoded values, but got this error System.Data.EntitySqlException: The right argument of the set expression must be of CollectionType. 

    :-

    RequestEntityDataSource.Where = $"it.Company.Id IN {141,200}  && " +
    -OR-
    RequestEntityDataSource.Where = $"it.Company.Id IN ({141,200}) && " +

    I managed get it works using loops. Hmmm, any better way than this?

    var companies = AvailableCompanies.Where(c=>!c.Id.Equals(0)).Select(c => c.Id).Distinct().ToArray<int>();
    			int[] markers = new int[companies.Count()];
    			string whereString = "";
    			for (int i = 0; i < markers.Length; i++)
    			{
    				if ((markers.Length - 1) != i) {
    					whereString += "it.Company.Id = " + companies[i] + " OR ";
    				}
    				else {
    					whereString += "it.Company.Id = " + companies[i];
    				}
    			}
    
    RequestEntityDataSource.Where = $"{whereString}  && " +



    Please advise.

    Thanks.

    Regards,
    Micheale

    Saturday, May 29, 2021 3:32 AM
  • User-1330468790 posted

    Hi mcinie2020, 

     

    If you only want to get a string for WHERE-IN clause, you might only need to call this function, String.Join Method, to concatenate the collection of strings.

    However, if you are using loops, you can make some logics inside loops as you shown in above codes.

      

    RequestEntityDataSource.Where = $"it.Company.Id IN {141,200}  && " +
    -OR-
    RequestEntityDataSource.Where = $"it.Company.Id IN ({141,200}) && " +

    The reason why you got error message is that you should not put "&&" at the end of WHERE clause. Apart from that, the right way to use IN clause on Where attribute of EntityDataSource is different from SQL statement. You have to put curly brace block surrounding all values like array declaration:

    Where="it.Company.Id IN {11,12,13,14}"

    instead of using parentheses.

      

    Hope helps.

    Best regards,

    Sean

    Monday, May 31, 2021 5:42 AM
  • User-893002196 posted

    Hi,

    Tested not work. && mean I still got others filters.... As you can see on the top there is full string of filters. Instead keep repeating typing in long. I just took out the parts to show in the code above. If you read from top post. Then you'll understand what I am trying to achieve. I have tested what you have suggestion but No luck. I do refer one of the post link above someone mentioned that. But however I still get error. I prefer to use String.Join but no luck. That's why i tried to hardcoded the value also no luck. But when I am doing loops then It working fine. I am looking for String.Join method which make the code clean & easy to read & save looping times as well. 

    Please advise

    Thanks.

    Regards,
    Micheale

    Tuesday, June 1, 2021 7:53 AM