Hi,

Sorry for the basic question but the syntax is eluding me

What would the Linq be for:

declare @user_id int set @user_id = 844

declare @user_id2 int set @user_id2 = 3343

select 'common', * from friends
   where user_id = @user_id  and friends_with_user_id in (select friends_with_user_id from friends where user_id = @user_id2)

select 'uncommon', * from friends
   where user_id = @user_id  and friends_with_user_id not in (select friends_with_user_id from friends where user_id = @user_id2)

Trying to find common friends between 2 users ('common'), and then just friends of 1 user ('uncommon').

Thanks.

Shawn

Hi Shawn, I think this would be an approximate solution:

Common friends

using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    LinqMetaData metaData = new LinqMetaData(adapter);

    var q = from u in metaData.Friends
                        where u.UserId == user1
                        && (from f in metaData.Friends
                              where f.UserId == user2
                              select f.FriendsWithUserId)
                                   .Contains(u.FriendsWithUserId)
            select u;

    List<FriendEntity> commonFriends= q.ToList();
}

Uncommon friends (note the ! on the InSet filter)

using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    LinqMetaData metaData = new LinqMetaData(adapter);

    var q = from u in metaData.Friends
                        where u.UserId == user1
                        && !(from f in metaData.Friends
                              where f.UserId == user2
                              select f.FriendsWithUserId)
                                   .Contains(u.FriendsWithUserId)
            select u;

    List<FriendEntity> unCommonFriends= q.ToList();
}

You can also use intersect for the common friends I think and Except for uncommon friends (haven't tested it for your situation though). David's examples are a good example of how 'Contains' should be used for IN query usage so they should also work

Hi,

Thanks - I think I'm still missing something or possibly tripping a bug.

I'm using version 2.6.0.0 dated 13 June 2008, DataAdapter against MSS2QL 2K.

The table is:

CREATE TABLE [dbo].[CREP_Friends](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [deleted] [bit] NOT NULL CONSTRAINT [DF_CREP_Friends_deleted]  DEFAULT (0),
    [friend_group_id] [int] NOT NULL CONSTRAINT [DF_CREP_Friends_friendship_type]  DEFAULT (1),
    [user_id] [int] NOT NULL,
    [friends_with_user_id] [int] NOT NULL,
    [monitor_activity] [bit] NOT NULL CONSTRAINT [DF_CREP_Friends_monitor_activity]  DEFAULT (0),
    [date_created] [datetime] NULL,
    [created_by] [int] NULL,
 CONSTRAINT [PK_CREP_Friends] PRIMARY KEY CLUSTERED 

I don't think the Intersect and Except operators work - no errors, but not the results I'm looking for

The code is:

            int userId1 = 844;
            int userId2 = 3343;

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                var metaData = new LinqMetaData(adapter);

                var q = from u in metaData.CrepFriends
                        where u.UserId == userId1
                        && (from f in metaData.CrepFriends
                            where f.UserId == userId2
                            select f.FriendsWithUserId)
                                 .Contains(u.FriendsWithUserId)
                        select u;

                ViewData["CommonFriends"] = q.ToList();

            }

The error that's generated is:

Invalid column name 'friends_with_user_id'. 

Stacktrace:

[SqlException (0x80131904): Invalid column name 'friends_with_user_id'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
   System.Data.SqlClient.SqlDataReader.get_MetaData() +62
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1005
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7
   SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +75

[ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Invalid column name 'friends_with_user_id'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.]
   SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +226
   SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery) +147
   SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) +894
   SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) +115
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteEntityProjection(QueryExpression toExecute) +195
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) +178
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) +20
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) +14
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() +36
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +369
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +54
   WebSite.Controllers.TestController.FindCommonFriends() in C:\NAIDirect\Projects\MVCCreplace.Com\CREPlace.com\WebSite\Controllers\TestController.cs:78

[TargetInvocationException: Exception has been thrown by the target of an invocation.]
   System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0
   System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +72
   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +308
   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29
   System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters) +17
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(MethodInfo methodInfo, IDictionary`2 parameters) in C:\Testing\VStudio2008\MVCPreview4\MVC\v1\Mvc\ControllerActionInvoker.cs:261
   System.Web.Mvc.<>c__DisplayClassc.<InvokeActionMethodWithFilters>b__9() in C:\Testing\VStudio2008\MVCPreview4\MVC\v1\Mvc\ControllerActionInvoker.cs:312
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) in C:\Testing\VStudio2008\MVCPreview4\MVC\v1\Mvc\ControllerActionInvoker.cs:290
   System.Web.Mvc.<>c__DisplayClasse.<InvokeActionMethodWithFilters>b__b() in C:\Testing\VStudio2008\MVCPreview4\MVC\v1\Mvc\ControllerActionInvoker.cs:318
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(MethodInfo methodInfo, IDictionary`2 parameters, IList`1 filters) in C:\Testing\VStudio2008\MVCPreview4\MVC\v1\Mvc\ControllerActionInvoker.cs:319
   System.Web.Mvc.ControllerActionInvoker.InvokeAction(String actionName, IDictionary`2 values) in C:\Testing\VStudio2008\MVCPreview4\MVC\v1\Mvc\ControllerActionInvoker.cs:229
   System.Web.Mvc.Controller.Execute(ControllerContext controllerContext) in C:\Testing\VStudio2008\MVCPreview4\MVC\v1\Mvc\Controller.cs:182
   System.Web.Mvc.Controller.System.Web.Mvc.IController.Execute(ControllerContext controllerContext) in C:\Testing\VStudio2008\MVCPreview4\MVC\v1\Mvc\Controller.cs:358
   System.Web.Mvc.MvcHandler.ProcessRequest(HttpContextBase httpContext) in C:\Testing\VStudio2008\MVCPreview4\MVC\v1\Mvc\MvcHandler.cs:65
   System.Web.Mvc.MvcHandler.ProcessRequest(HttpContext httpContext) in C:\Testing\VStudio2008\MVCPreview4\MVC\v1\Mvc\MvcHandler.cs:45
   System.Web.Mvc.MvcHandler.System.Web.IHttpHandler.ProcessRequest(HttpContext httpContext) in C:\Testing\VStudio2008\MVCPreview4\MVC\v1\Mvc\MvcHandler.cs:80
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +358
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

Generated SQL:

exec sp_executesql N'SELECT DISTINCT [LPLA_1].[id] AS [Id], [LPLA_1].[deleted] AS [Deleted], [LPLA_1].[friend_group_id] AS [FriendGroupId], [LPLA_1].[user_id] AS [UserId], [LPLA_1].[friends_with_user_id] AS [FriendsWithUserId], [LPLA_1].[monitor_activity] AS [MonitorActivity], [LPLA_1].[date_created] AS [DateCreated], [LPLA_1].[created_by] AS [CreatedBy] FROM [CREPlace].[dbo].[CREP_Friends] [LPLA_1]  WHERE ( ( ( ( ( [LPLA_1].[user_id] = @UserId1) AND  EXISTS (SELECT [LPA_L1].[FriendsWithUserId] FROM (SELECT [LPLA_2].[friends_with_user_id] AS [FriendsWithUserId] FROM [CREPlace].[dbo].[CREP_Friends] [LPLA_2]  WHERE ( ( ( [LPLA_2].[user_id] = @UserId2)))) [LPA_L1] WHERE ( [LPA_L1].[friends_with_user_id] = [LPLA_1].[friends_with_user_id]))))))', N'@UserId1 int,@UserId2 int', @UserId1 = 844, @UserId2 = 3343

It appears to be the final [LPA_L1].[friends_with_user_id] = [LPLA_1].[friends_with_user_id] in the SQL statement.

Thanks!

Shawn

Hi Shawn,

My test works nice with a similar request. Just to be sure, please update to the latest build (July 10).

Hi,

Thanks - I downloaded the newest build (10 Jul 2008 ) and the errors stopped and results are accurate using the Contains().

I didn't try the intersect method.

Just a couple of small items not related to the core:

• The installer doesn't display the ReadMe when checked after installing
• The stand-alone CHM download appears to be corrupted. The CHM in the installer seems okay.

Thanks again,

Shawn

shware wrote:

Thanks - I downloaded the newest build (10 Jul 2008 ) and the errors stopped and results are accurate using the Contains().

Great!

shware wrote:

• The stand-alone CHM download appears to be corrupted. The CHM in the installer seems okay.

It might be a security thing. Windows could ask you "Are you sure you want to open this file?" if you don't uncheck the "Always ask me" checkbox, the help content wont be displayed.

shware wrote:

Just a couple of small items not related to the core:

• The installer doesn't display the ReadMe when checked after installing

Thanks for the feedback, we will look into it.

The readme isn't showing indeed, while the command to show it is in the installer script. I'll see if we can fix this.

(edit). The error was in the installer script for NSIS: we passed the source folder name to the page dialog though it has to be the final installation folder location to show the readme. Fixed in next build.!