none
Linq like Sql server Where Case RRS feed

  • Question

  • I have a Linq, which I want to do something like in Sql Server, for example:

    Declare @id int
    select * from tbl1
    where col1=Case when @id IS NULL THEN col1
    else @id END

    couldn't find a way, is it possible?

    Thursday, February 13, 2020 3:19 PM

All replies

  • You can do something like this pattern and use Contains, StartsWith EndsWith and DbFunctions.Like

    if (Country != "All")
    {
        query = query.Where(p => p.Country == Country);
    }
    
    if (Income != "All")
    {
        query = query.Where(p => p.Income == Income);
    }
    
    if (Age != "All")
    {
        query = query.Where(p => p.Age == Age);
    }
    
    List<Person> fetchedPeople = query.ToList();

    Also look at some of my examples

    https://github.com/karenpayneoregon/SqlServerLikeConditionsComparisionEF_EF_Core


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, February 13, 2020 4:23 PM
    Moderator
  • Hi want 2 Learn,

    Thank you for posting here.

    Please check the following linq statement, it should be similar to the sql statement function you provided.

      var res = from cell in list
                where coll == "Case"
                select new
                {
                     Value  = @id ?? coll
                 };

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, February 14, 2020 2:59 AM
  • Hi Karen,

    @Karen

    I did something like this ( which included paging but not sure this is the best way) :

    var sends = from apisend in context.ApiTable
                        join provider in context.ProviderLists
                        on apisend.ProviderID equals provider.ProviderID
                        join
                        subaccount in context.SubAccounts
                        on apisend.ApiAccountID equals subaccount.SubAccountId
                        join
                        country in context.Countries
                        on apisend.CountryID equals country.ID
                        select new
                        {
                            LogID = apisend.ID,
                            ProviderName = provider.ProviderName,
                            ProviderID = provider.ProviderID,
                            subaccount.SubAccountName,
                            SendTime = apisend.CreatedDate,
                            Status = TranslateStatus(apisend.Status.Value),
                            StatusID=apisend.Status.Value,
                            AccountName = subaccount.Account.CompanyName,
                            CountryName = country.CountryName,
                            apisend.Credits
                        };

            and then did the If check's you proposed but I assume this way is wasting resources

    since first it fetch all...


    • Edited by want 2 Learn Sunday, February 16, 2020 4:55 PM a
    Sunday, February 16, 2020 4:54 PM
  • Hi,

    I agree.

    Based on your code analysis of your functions, I think your method (using linq) is probably the easiest method.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 18, 2020 1:19 AM
  • what do you mean by that?
    firsrt I bring all the data, only then i filter, which is very resource consuming.
    isn't there a better way of doing this?
    Tuesday, February 18, 2020 3:26 PM
  • Hi,

    After my search, there seems to be a way to improve: using expression trees.

    Please refer to the links below, they may be helpful.

    Does ORM (Linq-SQL EF) load entire data set from table?

    Manipulate your expression trees with elegance

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 19, 2020 7:35 AM