Linq to SQL distinct

Issue:

I needed to get a distinct value from a query like

select distinct o.ProgramID,t.Program
from table1 o
inner join table2 t on o.ProgramID=t.ProgramID
order by t.Program

var result = (from o in table1
              join t in table2 on o.ProgramID equals t.ProgramID
              orderby t.Program
              select new { o.ProgramID, t.Program }).Distinct();

https://stackoverflow.com/questions/4318909/linq-to-sql-distinct-and-orderby

Linq to SQL where clause against collection of list values

Problem:

I was working on an EF Model with Linq 2 SQL.  

I sometimes depend on the SQL sp's which depend upon subqueries

select x.value1,x.value2 from myTable x

where

x.primarykey in (select foreignkey from myTable2 where name="jump")

How do you accomplish this in Linq?

Solution:

var innerquery = (from iq in myTable2

where iq.name.Equals("jump")

select iq.id);

var Ids = innerquery.ToList();

var query = 

from mt in myTable

where

Ids.Any(qq => mt.Id.Equals(qq)

select mt);

Source:

http://stackoverflow.com/questions/1075540/linq-to-sql-how-to-do-where-column-in-list-of-values

Unable to create a constant value of type 'System.Object'

Problem:

I had a class created using the EF tools.  It generated a field which was a nullable Guid.  The datatype declaration was:


Guid? Status_ID


Each time I ran my controller code to build out my query.  I ran the code


Guid? ms_ID = Guid.Parse(MilitaryStatus_ID);
predicate = predicate.Or(l => l.MilitaryStatus_ID.Equals(ms_ID));


This would always through the unknown system.object error.  It was driving me crazy.

Solution:

I did not have any issues against data types of Guid which lead me to think about the nullable operator.  This seemed to be the issue so I removed it for testing and was able to run the above code no problems.  Thus realized that the issue was Linq not dealing with the nullable condition of my variable correctly.  Thus the work around was  to use == instead of Equals operator.


Guid? ms_ID = Guid.Parse(MilitaryStatus_ID);
predicate = predicate.Or(l => l.MilitaryStatus_ID == ms_ID);

Source:

http://stackoverflow.com/questions/4592432/linq-query-keeps-throwing-unable-to-create-a-constant-value-of-type-system-obje

Inner Join using LINQ

Create query:

var queryOfficeInfo = from ofc in db.Offices

join llo in db.LLOs
on ofc.Office_Name equals llo.LLO_Name
select new

{

ofc.Office_ID,

llo.tp_ID,

ofc.Office_Name

};

Retrieve Query:



var result = queryOfficeInfo.Where(q => q.tp_ID.Equals(obj.LLO_ID));

//Update the FCJC Record to reflect correct office relationship

obj.Office_ID = result.Select(a => a.Office_ID).FirstOrDefault();

LINQ to SQL equivalent of select top

Problem

I wanted to get the top 10 rows from a linq query.  I was not sure what the LINQ syntax was.

Solution

var q = from m in MyTable.Take(10)  
select m

or 


if you'd like to get the items from 30 to 60
 
var foo = (From t In MyTable Select t.Foo).Skip(30).Take(30);