locked
order by RRS feed

  • Question

  • User932259438 posted

    hi,

    i would like to ask you how can i used GetAmountAff orderby descending.

    My code:

     

     var showbind = (from t1 in db.tbl_RegistrationPartners
                                join t2 in db.aspnet_Memberships
                                on t1.UserId equals t2.UserId
                                join t3 in db.aspnet_UsersInRoles
                                on t1.UserId equals t3.UserId
                                where t1.ApplicationId == applicationid() && t2.IsApproved == true && t3.RoleId == new Guid("F03F89DB-6CBA-4B81-B75B-89BB08B0BD96") &&
                                (t1.FirstName.Contains(txtSearch.Text.Trim()) ||
                                 t1.LastName.Contains(txtSearch.Text.Trim()) ||
                                 t1.CompanyName.Contains(txtSearch.Text.Trim()) ||
                                 t1.ContactPhone.Contains(txtSearch.Text.Trim()) ||
                                 t1.CellPhone.Contains(txtSearch.Text.Trim()) ||
                                 t1.SkypeName.Contains(txtSearch.Text.Trim()) ||
                                 t1.Address1.Contains(txtSearch.Text.Trim()) ||
                                 t1.Address2.Contains(txtSearch.Text.Trim()) ||
                                 t1.State.Contains(txtSearch.Text.Trim()) ||
                                 t1.Zip.Contains(txtSearch.Text.Trim()) ||
                                 t1.Country.Contains(txtSearch.Text.Trim()) ||
                                 t1.Currency.Contains(txtSearch.Text.Trim()) ||
                                 t1.WebsiteUrl.Contains(txtSearch.Text.Trim()) ||
                                 t1.Question.Contains(txtSearch.Text.Trim()) ||
                                 t1.Answer.Contains(txtSearch.Text.Trim()) ||
                                 t1.AffiliateCode.Contains(txtSearch.Text.Trim()))
    
                                select new
                                {
                                    t1.UserId,
                                    t1.FirstName,
                                    t1.LastName,
                                    t1.CompanyName,
                                    t1.ContactPhone,
                                    t1.CellPhone,
                                    t1.SkypeName,
                                    t1.Address1,
                                    t1.Address2,
                                    t1.State,
                                    t1.Zip,
                                    t1.Country,
                                    t1.Currency,
                                    t1.WebsiteUrl,
                                    t1.Question,
                                    t1.Answer,
                                    t1.AffiliateCode,
                                    t2.CreateDate,
                                    GetAmountAff = GetAmountAff(new Guid(t1.UserId.Value.ToString())),
                                    AmountPartnerCommission = t1.CommissionPartner,
                                }).OrderByDescending(ui => ui.GetAmountAff).ToList();

    I tried like that but i got

    Method 'System.Decimal GetAmountAff(System.Guid)' has no supported translation to SQL.

    Monday, February 20, 2017 8:59 PM

All replies

  • User765422875 posted

    You are calling GetAmountAff and within what is an expression tree. The compiler tries to compose that tree to SQL.
    Bottom line, its is not possible to translate that function to SQL. A possile workaround is to make most of
    your query with LINQ to SQL and then use LINQ to Objects for the rest.

    Monday, February 20, 2017 9:20 PM
  • User-832373396 posted

    Hi progy85,

    I tried this code below and it works .

      var r = from c in db.BookMasters select new { strBookTypeId = new Guid("9D2B0228-4D0D-4C23-8B49-01A698857709").ToString() };

    it will generate the SQL code below:

    {SELECT 
        1 AS [C1], 
        LOWER( CAST( cast('9d2b0228-4d0d-4c23-8b49-01a698857709' as uniqueidentifier) AS nvarchar(max))) AS [C2]
        FROM [dbo].[BookMasters] AS [Extent1]}

    So maybe you modify your code with this :

    GetAmountAff = new Guid(t1.UserId.Value.ToString()).ToString(),

    Best regards,

    Jolie

    Tuesday, February 21, 2017 5:27 AM