Archive for July 2013
Udemy

Sql NOT IN operator in LINQ Query Entity Framework

Monday, July 29, 2013 0 Comments A+ a-

Few days ago, i came across a scenario in which i needed to apply sql NOT IN operator in my project as LINQ Query, my project was in mvc and i used ADO.net Entity Data Model to interact with database.


Here is the sql query which i needed to convert to linq:



SELECT d.processid,d.name,t.test_name
FROM dc_tp_tprocess d
left outer join dc_tp_tprocedured pd on pd.processid=d.processid
left outer join dc_tp_test t on t.procedureid=pd.procedureid
where d.active='Y' and t.testid=1 
                   and d.processid not in (1,2,6,8,9,10)
order by t.testid,d.processid


At last after posting my question on a forum i got an answer from some one and that was this:



int[] tmp=new int[]{1,2,6,8,9,10};

var query=from d in dc_tp_tprocess
          join pd in dc_tp_tprocedured on d.processid equals pd.processid 
         into leftGroup1
          from pd in leftGroup1.DefaultIfEmpty() 
          join t in dc_tp_test on pd.procedureid equals t.procedureid 
          into leftGroup2
          from t in leftGroup2.DefaultIfEmpty()
          orderby t.testid,d.processid
          where d.active=="Y" 
         && t.testid==1 
         && !tmp.Contains(d.processid)
          select new {d.processid,d.name,test_name=t==null?"":t.test_name};


The tmp is a temporary array taken from which i am checking that the processID coming in the current row not exists in that array then select row.

I hope it will help if you stuck or got same scenario and don't getting how to handle the situation -:).
Udemy

Error 25 The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'

Sunday, July 28, 2013 0 Comments A+ a-

I had a scenario where i needed to fetch Result Data from multiple tables on basis of some condition by applying joins between the tables using Entity Framework. I was using Linq queries as in EF linq query is faster than the normal string queries and stored procedures, but i was getting the exception.

Here is the query which i wrote:

I made the object of my EntitiesClass generated by EDM:


OLSContainer ols = new OLSContainer();
        var reSet = from l in ols.LEVEL_COURSE
                    join lp in ols.PACKAGES 
                    on new { l.course_ID, l.levelID } 
                    equals new { lp.course_ID, lp.level_ID }
                    select l;
which i know was wrong and on this query i was getting this exception:

Error 25 The type of one of the expressions in the join clause is 
incorrect. Type inference failed in the call to 'Join'

The mistake i was making was pretty was pretty simple in this scenario i dont need the join because i was only selecting data from LEVEL_COURSE table which can be achieved by simple where clause in the current scenario like this:



OLSContainer ols = new OLSContainer();
        var reSet = (from l in ols.LEVEL_COURSE
                    from p in ols.PACKAGES
                    where l.course_ID == p.course_ID 
                    && l.levelID == p.level_ID 
                    &&    l.course_ID==courseID
                    select new { l.levelID, l.levelName }).Distinct();

This solved my problem and the exception that i was getting was due to column names were same. My  PACKAGES table and LEVEL_COURSE table both have column named course_ID which were of same datat type as well so it was confusing compiler to take which one, but at last i got working thing as i needed.


Reference Link:

http://stackoverflow.com/questions/14273879/error25the-type-of-one-of-the-expressions-in-the-join-clause-is-incorrect-typ/14290833#14290833

Subscribe to: Posts (Atom)