.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

join with linq

Posted By:      Posted Date: October 01, 2010    Points: 0   Category :ASP.Net
 

how to set right join with LINQ


:)




View Complete Post


More Related Resource Links

Linq join fields for return data

  

Hello,
  how would I join fields together?

           return (from c in storedb.Product_Categories

                         where c.Category_Name.Contains(searchText)
                         orderby c.Category_Name
                         select new {
                                    c.Cat_GUID,
                                    c.Category_Key && " ;" && c.Category_Name // HOW CAN I DO THIS.....
                          

Get Distinct value from a Group-Join syntax Linq

  
Hello,I have this group-join syntax but I couldn't get the distinct value from the relational tables below.Table_1key_1 t1_value1     Food2     Sports3     Leisure 4     Trip5     ZooTable_2key_2   key_1  t2_boolean15        1          True16        1          True17        1          True18        2          True19        2          True20        2          Falsedesire result:FoodSports            Dim c = (From t1 In dbContext_Table_1 _                                     Group Join GroupTable In _db.Table_2 On GroupTable.key_1 Equals t1.key_1 Into Group 

LINQ left outer join question

  
I can do this in other ways, but am looking for the "LINQ" way of doing this: Table1: People (PersonId,SchoolId)Table2: AssignedPeople (PersonId,SchoolId,RoomId) I want to select the people in Table1 within a particular SchoolId that are not in the assignedPeople table (in a particular Room).  The SQL is: select p.* from people p left outer join assignedpeople ap on p.PersonId=ap.Person and p.SchoolId=ap.SchoolIdand ap.RoomId=@roomId where p.SchoolId=@SchoolId and ap.PersonId is null I tried this LINQ (VB,NET):dim SchoolID as guid=...dim RoomID as guid=... (from p in dc.people where p.SchoolId=SchoolIdgroup join ap in dc.AssignedPeopleon p.PersonId equals ap.PersonId and p.SchoolId equals ap.SchoolId into groupfrom g in group.DefaultIfEmptywhere g.RoomID = nothing) but I can't get the ap.RoomId=@roomId part into the right place, should be something like:group join (from ap in dc.AssignedPeople where ap.RoomId=RoomId) Any help would be appreciated,Reuven

LINQ-to-SQL: How do you join more than 2 tables?

  
Hi there, I was trying to find out how I can join more than 2 tables using the LINQ-to-SQL syntax. For instance, joining 2 tables in SQL:  SELECT * FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Key=T2.Column1WHERE T2.Key='17'; can be expressed as: var Result = from T1 in DbContext.Table1 join T2 in DbContext.Table2 on T1.Key equals T2.ForeignKey where T2.Key=17 select new { T1, T2 }; But how would I join 3 or more tables using LINQ? For example: SELECT * FROM (Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Key=T2.Column1) INNER JOIN Table3 AS T3 ON T3.Key=T2.Column2 WHERE T2.Key='37'; I've been searching and experimenting and I cannot seem to find any informraiton on this. One example I found involves putting the result for the frist join into a temp object, and then performing the second join. I'm not sure performance-wise if that's the same as doing a 3-table join directly using a single SQL statement. Any help will be greatly appreciated! Thanks,- K.  

Left Outer Join in LINQ + Entities

  
HiI have found quite a bit of info on the net, but I can't get it to work. Here's the normal SQL query:SELECT cl.ID AS ClientID, cl.Name, cl.Surname, CLC1.PHONECODE + ' ' + CLC1.PHONENUMBER AS WorkNum, CLC2.PHONECODE + ' ' + CLC2.PHONENUMBER AS HomeNum, CLC3.PHONECODE + ' ' + CLC3.PHONENUMBER AS CellNum, CLC1.PREFERRED AS WorkNumPref, CLC2.PREFERRED AS HomeNumPref, CLC3.PREFERRED AS CellNumPref, veh.ID AS VehicleID, veh.MAKE, veh.MODEL, veh.YEAR, veh.REGNO, ws.ID AS WorkshopID, ws.JOBTYPE, ws.SERVICEADVISOR, ws.TIMEFINISHED, ws.TECHNICIAN, fs.ID as FollowupID, fs.TYPE, fs.DESCRIPTION, fs.SCHEDULEDATE, fs.WRONGNUMBERFLAG FROM CLIENTS AS cl LEFT OUTER JOIN CLIENTCONTACTNUMBERS AS CLC1 ON cl.ID = CLC1.CLIENTID AND CLC1.TYPE = 'W' LEFT OUTER JOIN CLIENTCONTACTNUMBERS AS CLC2 ON cl.ID = CLC2.CLIENTID AND CLC2.TYPE = 'H' LEFT OUTER JOIN CLIENTCONTACTNUMBERS AS CLC3 ON cl.ID = CLC3.CLIENTID AND CLC3.TYPE = 'C' INNER JOIN CLIENTVEHICLES AS clveh ON cl.ID = clveh.CLIENTID INNER JOIN VEHICLES AS veh ON clveh.VEHICLEID = veh.ID INNER JOIN WSBOOKINGS AS ws ON clveh.ID = ws.CLIENTVEHICLEID INNER JOIN FOLLOWUPSCHEDULE AS fs ON fs.WSBOOKINGID = ws.ID WHERE fs.ID = 2 There is a multiple join between CLIENTS and CLIENTCONTACTNUMBERS so it would return a single row of data. If you were to do a normal join on CLIENTS and C

left outer join linq

  
 Hi   I try to get this exsisting linq query to use a left outer join without success, can anyone show me how its supposed to be?   Dim product = (From f In db.tbl_Formats _ Join p In db.tbl_Products On f.FormatID Equals p.FormatID Where p.NodeId = iNodeID _ Select p, f.FormatName).SingleOrDefault   The FormatID is not always present and I therefore need a left outer join on the Formats table.   Best Regards

Join linq queries with same field name

  
Hi everyone-I'm trying to join three Linq queries together.  One of the fields (rating) is the same name for each so when I add b.rating as you'll see below, I get a squiggly that says "Anonymous type member or property 'rating' is alread declared.   Dim customerCategoryA= (From a In dc.CategoryARatings _                                  Where a.userID = userID _                                  Select New With {a.productID, a.rating, a.ratingComment}) Dim customerCategoryB = (From b In dc.CategoryBRatings _                                 Where b.userID = userID _                                 Select New With {b.productID, b.rating}) Dim DataForGridview = From p In Products _   

LINQ to Entity - using join for multiple table

  

I have my SQL query which needs to be conerted to Linq to Entity,

select pfr_sa.SID,pfr_sa.SourceGroupID,tx.txroll_cadaccountnumber,tx.txroll_StreetNumb

Entity Framework - how to join tables without LINQ and with only string?

  

Hi all,

I have a question about Entity Framework. Please answer if you know answer on this. I have such query :

    String queryRaw =
        "SELECT " +
        "p.ProductName AS ProductName " +
        "FROM ProductEntities.Products AS p " +
        "INNER JOIN CategoryEntities.Categories AS c " +
        "ON p.CategoryID = c.CategoryID ";
   
    ObjectQuery<DbDataRecord> query = new ObjectQuery<DbDataRecord>(queryRaw, entityContext);
   
    GridView1.DataSource = query;
    GridView1.DataBind();



Particularly I want to join few tables in one query, but I can NOT use LINQ and can NOT use ObjectQuery with objects mapped to DB fields inside my query. Because each entity creates dynamically. So this is what i can NOT use :

http://msdn.microsoft.com/en-us/library/bb425822.aspx#linqtosql_topic12

http://msdn.microsoft.com/en-us/library/bb896339%28v=VS.90%29.aspx

The question is can I use something like this instead of using objects?

query.Join ("INNER JOIN CategoryEntities.Category ON p.CategoryID = c.CategoryID ");

Dynamic LINQ Outer Join

  

Hey folks,

I want to do an outer join on an IQueryable without LINQ.

I have this query on all my services which differs only by one property reference. I want to abstract it away but I need to substitute the property reference with something I can pass in as an argument.

Heres the query:


var secureList = (from l in list
                join ep in meta.EntityEffectivePermissions on new { EntityId = l.AnnouncementId, EntityType = "Announcement" } equals new { EntityId = ep.EntityId.Value, EntityType = ep.Name } into tempEEP
                from eep in tempEEP.DefaultIfEmpty()
                where !SecurityState || (eep.UserId == UserId && ((int)eep.Permissions & (int)Shivam.ASL.Core.Security.Permission.List) == (int)Shivam.ASL.Core.Security.Permission.List)
                select new { EntityId = l.AnnouncementId, Permissions = eep.Permissions.GetValueOrDefault() }).Distinct();


So the l.AnnouncementId (and obviously the string that I set to EntityType but that's easy) differ. I figured I could write this query using the IQueryable Join method but the documentation on this is just awful. I have found an example on how to use it but I am unsure as how to achieve a left join effect with it. Can someone help?



Regards,
Sam


join 3 table in LINQ

  

hi

i use Default membership provider for my membership

i add a profile table for some more information from user and it related with UserId with aspnet_Users

to get data from my database i use  Entity Data Model

now i want to write a Linq to get data from all 3 tables

can you help me to know how to write this guerry

thanks

 


Help with LINQ left join

  

Hi,

Im having a little trouble getting the syntax right in the following LINQ query.

            var merchants =
from m in dtM.AsEnumerable()
join mp in dtMP.AsEnumerable()
on m.Field<int>("MerchantId") equals mp.Field<int>("MerchantId")
into tmpProfile
from t in tmpProfile.DefaultIfEmpty()
where m.Field<string>("Name").ToLower().Contains(searchText.ToLower()) ||
t.Field<string>("Profile").ToLower().Contains(searchText.ToLower())
select m.Field<int>("MerchantId");

The problem is the highlighted line where I'm attempting to make a decision on a field that is potentially null/empty.  Anyone know how I get round this?


Thanks


Any opinions on using Projection or explicit join syntax when joining entities in Linq to SQL?

  

Folks,

I would like to know whether people use the join syntax OR projection when joining entities in LINQ to SQL queries. I have noticed that when using projection that a CROSS JOIN is in the generated SQL

e.g. 

var query =
from o in Orders
from  oi in o.OrderItems
where oi.OrderId == o.OrderId

generates SQL something like...

FROM [Customer].[Orders] AS [t0]
CROSS JOIN [Customer].[OrderItems] AS [t1]
WHERE ([t1].[OrderId] = [t0].[OrderId]) AND ([t1].[OrderId] = [t0].[OrderId])

whereas with explicit joins I get a clean inner join..

var query =
from o in Orders
join oi in OrderItems on o.OrderId equals oi.OrderId

generates..

FROM [Customer].[Orders] AS [t0]
INNER JOIN [Customer].[OrderItems] AS [t1] ON [t0].[OrderId] = [t1].[OrderId] 

I know using explicit joins over projection is frowned upon, but I am wary of those cross joins.

Just wondering what other folks preferences are when using joins in L2SQL.

Cheers

K

 

 


Re: Complex Multi join in Linq

  

Hi

Can anyone please help me out with the following sql query that I was trying to convert into a Linq query... I came half way through but am still stuck at at the middle... esp with the Max that I have to apply:

 

 SELECT 
       CNAME			
      ,C.FTYPE_ID
      ,CASE WHEN MAX(E.VSHT_ID) > 0 THEN 1 ELSE 0 END AS ISENABLED
  FROM [VSNAPSHOT] C 
  
 LEFT JOIN FSETTINGS E 
     ON (C.VSNAPSHOT_ID = E.VSNAPSHOT_ID)
  
 LEFT JOIN EXTERNAL_FEEDS EF 
     ON (EF.EXTERNAL_FEED_ID = E.EXTERNAL_FEED_ID)
  
 INNER JOIN [TIME_TIME] T
     ON (C.TIME_ID = T.TIME_ID)
 
 INNER JOIN DVERSION D 
     ON DVERSION_ID = C.DVERSION_ID
 
 INNER JOIN FTYPE F 
     ON F.FTYPE_ID = C.FTYPE_ID

  INNER JOIN D_CASE S 
     ON (S.CNAME =  FNAME + TNAME + VNAME)
 
 GROUP BY CNAME,C.FTYPE_ID

Also as a performance tip how good is it to have such a long linq query?

Any help is appreciated.

 

Thanks


LINQ JOIN

  

Hi,

        var Najdinaloge = from p in db.tbl_PotniNalogZaMestneVoznjes
                          join oList in db.tbl_RelacijeZaMestneVoznjes
                          on new { p.ID}
                          equals
                          new { oList.IDRelacija }
                          select p;


tbl_PotniNalogZaMestneVoznjes:

Column ID primary key


tbl_RelacijeZaMestneVoznjes

Column IDRelacija int

Error:

Compilation Error

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

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

Source Error:


Line 75:         //Najdi naloge
Line 76: var Najdinaloge = from p in db.tbl_PotniNalogZaMestneVoznjes
Line 77: join oList in db.tbl_RelacijeZaMestneVoznjes
Line 78:

MVC Linq Join List Question

  

This is the query I have

    Function Index() As ActionResult

            ViewBag.dateNow = DateTime.Now

            Dim result = (From newLocation In DataAccess.Locations Join
                         newState In DataAccess.States
                         On newLocation.state Equals newState.id
                         Select newLocation, newState)


            '   Return View(DataAccess.Locations.ToList)
            Return View(result.tolist)

        End Function

I am trying to populate the ToList View, but I can't figure out how to call a specific column since it is using a join...

Example:   @item.address

I was thinking it would be like   @item.newlocation.address

However, that is not working --- any advice?


Playing with Linq grouping: GroupByMany?

  
One of its features is grouping. Many people understand grouping like it is defined in Sql. Linq is implementing grouping quite the same way. Let's discover this syntax and how to make consecutive groups easier.
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend