linq
Showing posts with label linq. Show all posts
Showing posts with label linq. Show all posts

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

Udemy
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

Read More

Sql NOT IN operator in LINQ query Entity Framework

Udemy
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 -:).

Read More

How to Deserialize Xml using LINQ

Udemy
Binding XML data to Model in C# MVCToday i saw a SO question where OP had xml in a string and OP wanted to deserialize to its specifc type using linq.

Here is the xml:


<people>  
            <person>
                <name>Mr Brown</name>
                <age>40</age>
                <hobby>
                    <title>Eating</title>
                    <description>eats a lot</description>
                </hobby>
                <hobby>
                    <title>Sleeping</title>
                    <description>sleeps a lot</description>
                </hobby>
            </person>
            <person>
                <name>Mr White</name>
                <age>40</age>
                <hobby>
                    <title>Skiing</title>
                    <description>more details on this</description>
                </hobby>
                <hobby>
                    <title>Football</title>
                    <description>watches football</description>
                </hobby>
            </person>
            </people>

First of all we need to create class type in which the data will be deserialized.We have main node people so our one class will be People with three properties Name,Age and Hobbies.As hobbies can be multiple of a person we will need a collection of hobbies and you can observe that from the xml as well that person have multiple hobbies and of course i also have many hobbies..:)


here it is: 


public class People
        {
            public string Name { get; set; }
            public string Age { get; set; }
            public IList<Hobby> Hobbies { get; set; }
        }
        public class Hobby
        {
            public string Title { get; set; }
            public string Description { get; set; }
        }

First of all i will load the string xml in to XElement :


string xml = @"<people>  
            <person>
                <name>Mr Brown</name>
                <age>40</age>
                <hobby>
                    <title>Eating</title>
                    <description>eats a lot</description>
                </hobby>
                <hobby>
                    <title>Sleeping</title>
                    <description>sleeps a lot</description>
                </hobby>
            </person>
            <person>
                <name>Mr White</name>
                <age>40</age>
                <hobby>
                    <title>Skiing</title>
                    <description>more details on this</description>
                </hobby>
                <hobby>
                    <title>Football</title>
                    <description>watches football</description>
                </hobby>
            </person>
            </people>";

XElement element=XElement.Parse(xml);

Now we will write linq query to deserialize the xml. Here we go:


var person = from a in element.Descendants("person")
             select new People
             {
              Name = a.Element("name").Value,
              Age = a.Element("age").Value,
              Hobbies = a.Descendants("hobby")
                         .Select(x => new Hobby
                                 {
                                  Title = x.Element("title").Value,
                                  Description = x.Element("description").Value
                                 }).ToList()
                            };
  
foreach(var d in person)
{
  
  Console.WriteLine(String.Format("Name = {0}:Age={1}:Hobbies:{2}",d.Name,d.Age,d.Hobbies.Count()));
  
}

Here is theWorking Example on DOT NET FIDDLE of the code.


Cheers! Happy Coding.

Stackoverflow Reference Question:

Binding XML data to Model in C# MVC

Read More
Subscribe to: Posts (Atom)