Return empty string condition in query using Entity framework doesn't work

Question

I am trying to execute a query using EF6 as you can see here :

List<QC> listOfJoinWithoutDSWeldAndWithoutPGZfitup =
                qcs.Where(i => i.FINAL_WELD_NO == "" && i.SUBJECT == "").ToList();

So it returns 0 record.But in fact i have 16000 records that include this condition why it happens ?for proving i execute the query inside the sql as you can see :

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT *
  FROM [PGC].[dbo].[QC] where SUBJECT='' and [FINAL WELD NO]=''

It returns 16000 records why ?

qc structure :

namespace PGZ_CO
{
    using System;
    using System.Collections.Generic;

    public partial class QC
    {
        public string TP_No { get; set; }
        public string UNIT { get; set; }
        public string LINE_NO { get; set; }
        public string JOINT_NO { get; set; }
        public string SH { get; set; }
        public Nullable<double> SIZE { get; set; }
        public string FIT_UP_IN_NUMBER { get; set; }
        public string WELDING_IN_NO { get; set; }
        public string FITER_NAME { get; set; }
        public string HEAT_NO1 { get; set; }
        public string HEAT_NO2 { get; set; }
        public string SUBJECT { get; set; }
        public string INS { get; set; }
        public string WELDER_NUMBER1 { get; set; }
        public string REV { get; set; }
        public string Type { get; set; }
        public string SP_NO { get; set; }
        public string MAT { get; set; }
        public string NDT { get; set; }
        public string PIP_CLASS { get; set; }
        public string THk { get; set; }
        public string SHOP_FIELD { get; set; }
        public string WPS_NO { get; set; }
        public Nullable<System.DateTime> FIT_UP_IN_DATE { get; set; }
        public string TACK_WEL { get; set; }
        public string F { get; set; }
        public string ACC { get; set; }
        public string SUB_CO { get; set; }
        public string WELDED { get; set; }
        public Nullable<System.DateTime> WELDING_IN_DATE { get; set; }
        public string WELDER_NUMBER2 { get; set; }
        public string WELD_ARGON1 { get; set; }
        public string WELD_ARGON2 { get; set; }
        public string W { get; set; }
        public string REJ { get; set; }
        public string SUB_CO_WELD { get; set; }
        public string REMARK_FIT { get; set; }
        public string REMARK_WELD { get; set; }
        public string SORAT_VAZIYAT { get; set; }
        public string RT { get; set; }
        public string RT_REQ_NO { get; set; }
        public string RT_REQ_DATE { get; set; }
        public string RT_REPORT_NO { get; set; }
        public string RT_REPORT_DATE { get; set; }
        public string LAST_REPORT { get; set; }
        public string REMARK_RT { get; set; }
        public string RT_RESULT { get; set; }
        public string PT { get; set; }
        public string REQ_PT_NO { get; set; }
        public string REQ_PT_DATE { get; set; }
        public string REPORT_PT_NO { get; set; }
        public string REPORT_PT__DATE { get; set; }
        public string RESULT_PT { get; set; }
        public string REMARK_PT { get; set; }
        public string SCH { get; set; }
        public string PWHT_REQ_NO { get; set; }
        public string PWHT_REQ_DATE { get; set; }
        public string PWHT_REPORT_NO { get; set; }
        public string PWHT_REPORT_DATE { get; set; }
        public string REMARK_PWHT { get; set; }
        public string RESULT_PWHT { get; set; }
        public string SUB_CONTRACTOR { get; set; }
        public string FINAL_FIT_UP_NO { get; set; }
        public string FINAL_FIT_UP_DATE { get; set; }
        public string FINAL_WELD_NO { get; set; }
        public string FINAL_WELD_DATE { get; set; }
        public string PIP1 { get; set; }
        public string PIP2 { get; set; }
        public string REV_1 { get; set; }
        public string WELD_REPAIR_NO { get; set; }
        public string WELD_REPAIR_DATE { get; set; }
        public string RESULT_REPAIR { get; set; }
        public string RT_TYPE { get; set; }
        public string SPEC { get; set; }
        public string REMARK_WJCS { get; set; }
        public bool RT_1 { get; set; }
        public bool PT_1 { get; set; }
        public bool PWHT_1 { get; set; }
        public string PWHT { get; set; }
    }
}

Best regards

Use SQL Server Profiler to see what is _actually_ executed against your database. — ken2k, Nov 03 '14 at 14:07
@ken2k: If you did not know already: With EF 6 you can also use e.g. `entities.Database.Log = s => Trace.Write(s);` to get the executed SQL (and some additional infos) without the need for SQL Server Profiler... — Christoph Fink, Nov 03 '14 at 15:08
@ken2k: True, but in his first senctence he writes `using EF6`... — Christoph Fink, Nov 03 '14 at 15:33

score 2 · Accepted Answer · answered Nov 03 '14 at 14:50

Don't do the filtering on an in-memory list, it's considerably faster and more efficient to do this all in the database. For example, imagine the situation where you have 50000 records and you only need 5, it's horribly inefficient to drag all records just to get those few. So instead do this:

IEnumerable<QC> listOfJoinWithoutDSWeldAndWithoutPGZfitup =
            entities.QCs.Where(i => i.FINAL_WELD_NO == "" && i.SUBJECT == "");

Note:

Querying the context directly.
Not using ToList to keep the list as queryable.
Storing the result as IEnumerable so we don't lose the link to the context.

So now you can loop through the list as before, but you can also requery the list which will in turn query the database, which is way more efficient, for example:

IEnumerable<QC> smallerList = listOfJoinWithoutDSWeldAndWithoutPGZfitup
                                  .Where(q => q.RT_1 == true);

Sorry, but you are wrong there (see e.g. http://stackoverflow.com/questions/2876616/returning-ienumerablet-vs-iqueryablet). `IEnumerable` will also give you deferred execution, but **NOT** against the DB! (down-vote will be removed if fixed) — Christoph Fink, Nov 03 '14 at 15:04

Return empty string condition in query using Entity framework doesn't work

1 Answers1