could not translate expression into sql

Question

With the below code I get this error

InvalidOperationException:

Could not translate expression stok_getmiktar_byyapilanislem(urunler.ID, "SATIS") into SQL and could not treat it as a local expression.

var urundb = (from urunler in db.TBLP1URUNs
              orderby urunler.SERIAL
              where (urunler.ID.Contains(FilitreText) || urunler.URUNADI.Contains(FilitreText))
              && ((kategori1.SERIAL == null) || urunler.SERIAL.StartsWith(kategori1.SERIAL))
              select new
              {
                  UrunNo = urunler.ID,
                  UrunAdi = urunler.URUNADI,
                  Marka = urunler.MARKA,
                  SatisFiyati = urunler.SATFIYAT1.GetValueOrDefault(0),//.ToString() + " " + urunler.SATFIYAT1BIRIM,
                  TedarikFiyati = urunler.TEDFIYAT1.GetValueOrDefault(0),//.ToString() + " " + urunler.TEDFIYAT1BIRIM,
                  PiyasaFiyati = urunler.SATFIYAT2.GetValueOrDefault(0),//.ToString() + " " + urunler.SATFIYAT2BIRIM,

                  Hizmet = (urunler.HIZMETYENSURYIL > 0 && urunler.HIZMETYENSURAY > 0 ?
                                    urunler.HIZMETYENSURYIL.ToString() + " Yıl " + urunler.HIZMETYENSURAY.ToString() + " Ay"
                                : (urunler.HIZMETYENSURYIL > 0 && urunler.HIZMETYENSURAY <= 0 ?
                                        urunler.HIZMETYENSURYIL.ToString() + " Yıl"
                                    : (urunler.HIZMETYENSURYIL <= 0 && urunler.HIZMETYENSURAY > 0 ?
                                            urunler.HIZMETYENSURAY.ToString() + " Ay"
                                            : ""))),
                  TedarikEdilenMiktar = DAOUrun.stok_getmiktar_byyapilanislem(urunler.ID, "TEDARIK"),//this one works fine
                  SatilanMiktar = DAOUrun.stok_getmiktar_byyapilanislem(urunler.ID, "SATIS"),//this one works fine                              

                  Stok = urunler.TEDARIKCISTOKMIKTAR.GetValueOrDefault(0) 
                       - DAOUrun.stok_getmiktar_byyapilanislem(urunler.ID, "SATIS")//error occurs with this one

              });

below is stok_getmiktar_byyapilanislem

public static double stok_getmiktar_byyapilanislem(string urun_id, string yapilanislem)
{
    VeriyazDBDataContext db = new VeriyazDBDataContext(); db.Connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;

    double miktar = (from rows in db.TBLP1ISLEMDETAYs
                     where rows.URUN_ID == urun_id && rows.TBLP1ISLEM.YAPILANISLEM == yapilanislem
                     select rows.MIKTAR).Sum().GetValueOrDefault(0);

    return miktar;
}

How can I solve it?

score 2 · Accepted Answer · edited May 23 '17 at 10:33

For those who are or who may be confronted with and suffered from this error check Custom Method in LINQ to SQL query and Calling functions in LINQ queries

they say we cannot call our own functions/methods in linq, actually we can but when trying to get something from that query it throws the referred exception in my question.What I really wonder is why this one

SatilanMiktar = DAOUrun.stok_getmiktar_byyapilanislem(urunler.ID, "SATIS"),

works but this one doesn't

Stok = urunler.TEDARIKCISTOKMIKTAR.GetValueOrDefault(0) - DAOUrun.stok_getmiktar_byyapilanislem(urunler.ID, "SATIS")

I just wrote some other method like this:

public static double GetMevcutMiktar(string urunId,double acilisMiktari) 
{
    double mevcutMiktar = 0;
    mevcutMiktar = acilisMiktari - stok_getmiktar_byyapilanislem(urunId, "SATIS")
    return mevcutMiktar;
}

And called it instead of

urunler.TEDARIKCISTOKMIKTAR.GetValueOrDefault(0) - DAOUrun.stok_getmiktar_byyapilanislem(urunler.ID, "SATIS")

could not translate expression into sql

1 Answers1